public partial class Share : Entity
    {
        /// <summary>
        /// Конструктор Паи
        /// </summary>
        /// <param name="id">уникальный идентификатор записи</param>
        public Share(Guid id)
            : base(id)
        {
        }

[Field]
        [Key]
        [Id("00000000-0000-0000-A0E2-AB84E1FC33A4")]
        public Guid Id { get; private set; }

        /// <summary>
        /// Фин. инструмент
        /// </summary>
        [Field(Nullable = false)]
        [Id("00000000-0000-0000-AD47-F3D0A397AA2E")]
        public FinTool FinTool { get; set; }

        /// <summary>
        /// Выражение для трансляции в SQL
        /// </summary>
        private static readonly Expression<Func<Share, string>> PduRegNumberExpression = e => e.FinTool == null ? null : Query.All<Fund>().FirstOrDefault(f => f.Person == e.FinTool.Emitter) != null ? Query.All<Fund>().FirstOrDefault(f => f.Person == e.FinTool.Emitter).PduRegNumber : null;

        /// <summary>
        /// Компилированное выражение для вызова в свойстве класса
        /// </summary>
        private static readonly Func<Share, string> PduRegNumberExpressionCompiled = PduRegNumberExpression.Compile();

/// <summary>
        /// Регистрационный номер правил ДУ
        /// Спр. Фондов поле Регистрационный номер правил ДУ
        /// </summary>
        [VirtualField]
        [Id("00000000-0000-0000-9F7C-99D0499814A2")]
        public string PduRegNumber
        {
            get { return PduRegNumberExpressionCompiled(this); }
        }

/// <summary>
        /// Класс-контейнер для связки вирт полей со свойствами класса
        /// </summary>
        [CompilerContainer(typeof(Expression))]
        public static class CustomLinqCompilerContainer
        {
            ///<summary>
            /// Связывает экспрешен с свойством класса
            ///</summary>
            ///<param name="assignmentExpression">Экспрешен для SQL</param>
            ///<returns>Результирующее выражение</returns>
            [Compiler(typeof(Share), "FinToolGroup", TargetKind.PropertyGet)]
            public static Expression ShareFinToolGroup(Expression assignmentExpression)
            {
                return finToolGroupExpression.BindParameters(assignmentExpression);
            }

            ///<summary>
            /// Связывает экспрешен с свойством класса
            ///</summary>
            ///<param name="assignmentExpression">Экспрешен для SQL</param>
            ///<returns>Результирующее выражение</returns>
            [Compiler(typeof(Share), "PduRegNumber", TargetKind.PropertyGet)]
            public static Expression SharePduRegNumber(Expression assignmentExpression)
            {
                return PduRegNumberExpression.BindParameters(assignmentExpression);
            }
        }
     }

[HierarchyRoot]
    [Id("3D4142E2-9A9E-43EE-0000-000000000000")]
    //[Index("Name", Unique = true, Name = "Person.IX_Name")]
    public partial class Person : Entity
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="T:Werp.Model.Models.Base.ReferenceBase"/> class.
        /// </summary>
        /// <param name="id">Идентификатор элемента</param>
        public Person(Guid id)
            : base(id)
        {
        }

/// <summary>
        /// Идентификатор
        /// Содержит уникальный Guid элемента.
        /// </summary>
        [Field]
        [Key]
        [Id("00000000-0000-0000-A0E2-AB84E1FC33A4")]
        public Guid Id { get; private set; }

        /// <summary>
        /// Идентификатор Int
        /// Идентификатор из мигрированных таблиц
        /// </summary>
        [Id("00000000-0000-0000-AB0C-1A2ABE603701")]
        [Field]
        public int IntId { get; set; }
}

[HierarchyRoot]
    [Id("8183DA48-F049-4157-0000-000000000000")]
    public class Fund : Entity
    {
        /// <summary>
        /// Initializes a new instance of the <see cref="T:Test.Model.Models.References.Fund"/> class.
        /// </summary>
        /// <param name="id">Идентификатор элемента</param>
        public Fund(Guid id)
            : base(id)
        {
        }

        [Field]
        [Key]
        [Id("00000000-0000-0000-A0E2-AB84E1FC33A4")]
        public Guid Id { get; private set; }

        /// <summary>
        /// Идентификатор Int
        /// Идентификатор из мигрированных таблиц
        /// </summary>
        [Id("00000000-0000-0000-AB0C-1A2ABE603701")]
        [Field]
        public int IntId { get; set; }

        /// <summary>
        /// Персона
        /// Ссылка на персону, которой назначена категория Фонд. Используется краткое наименование.
        /// </summary>
        [Field(Nullable = false)]
        [Id("00000000-0000-0000-A84B-6D0671F6BEFC")]
        public Person Person { get; set; }

        /// <summary>
        /// Рег.№ ПДУ
        /// Регистрационный номер правил ДУ
        /// </summary>
        [Field(Length = 50, Nullable = true)]
        [Id("00000000-0000-0000-8C0B-BA70B90B9CD6")]
        public string PduRegNumber { get; set; }

When I try to get list of Share, i get this SQL which is wrong

SELECT  [a].[Id] ,
        [a].[FinTool_Id] ,
        [a].[EmissionVolume] ,
        [a].[Version] ,
        [b].[#a.Name] ,
        [c].[#b.Id] ,
        [d].[#c.PduRegNumber]
FROM    ( SELECT TOP 41
                    [e].[Id] ,
                    191 AS [TypeId] ,
                    [e].[FinTool_Id] ,
                    [e].[EmissionVolume] ,
                    [e].[Version] ,
                    ROW_NUMBER() OVER ( ORDER BY [e].[Id] ASC ) AS [RowNumber0]
          FROM      [dbo].[Share] [e]
          WHERE     ( [e].[Version] < CAST('2010-07-15 00:00:00.000' AS DATETIME2) )
          ORDER BY  [e].[Id] ASC
        ) [a]
        INNER JOIN ( SELECT [f].[Id] AS [#a.Id] ,
                            146 AS [#a.TypeId] ,
                            [f].[IntId] AS [#a.IntId] ,
                            [f].[Emitter_Id] AS [#a.Emitter_Id] ,
                            [f].[FinToolGroup_Id] AS [#a.FinToolGroup_Id] ,
                            [f].[FinToolType_Id] AS [#a.FinToolType_Id] ,
                            [f].[Name] AS [#a.Name] ,
                            [f].[FullName] AS [#a.FullName] ,
                            [f].[Currency_Id] AS [#a.Currency_Id] ,
                            [f].[Nominal] AS [#a.Nominal] ,
                            [f].[Version] AS [#a.Version]
                     FROM   [dbo].[FinTool] [f]
                   ) [b] ON ( [a].[FinTool_Id] = [b].[#a.Id] )
        OUTER APPLY ( SELECT TOP 1
                                [g].[Id] AS [#b.Id]
                      FROM      [dbo].[Fund] [g]
                      WHERE     ( [g].[Person_Id] = [b].[#a.IntId] )
                      ORDER BY  [g].[Id] ASC
                    ) [c]
        OUTER APPLY ( SELECT TOP 1
                                [h].[Id] AS [#c.Id] ,
                                [h].[PduRegNumber] AS [#c.PduRegNumber]
                      FROM      [dbo].[Fund] [h]
                      WHERE     ( [h].[Person_Id] = [b].[#a.IntId] )
                      ORDER BY  [h].[Id] ASC
                    ) [d]
WHERE   ( [a].[RowNumber0] > 0 )
ORDER BY [a].[Id] ASC ,
        [b].[#a.Id] ASC ,
        [c].[#b.Id] ASC ,
        [d].[#c.Id] ASC ;

Msg 206, Level 16, State 2, Line 1 Operand type clash: uniqueidentifier is incompatible with int

This is because it tries to join INT and GUID fields like

WHERE     ( [h].[Person_Id] = [b].[#a.IntId] )

Updated at 09.08.2010 11:47:22

Guys?


Updated at 13.08.2010 15:31:32

as for 1) This code is executed not only in sql but also in out BL, where Person can be null thus leading into NullreferenceException. Moreover, it seems like you "optimize" this into smth like

( CASE WHEN ( CAST(1 AS BIT) <> 0 ) THEN @p0_1
                                                                  ELSE [c].[ActualizationDate]
                                                             END ) but this is wrong, when parameter goes null, so SQL server must generate new query plan

as for 2) Yes, this might be done

This thread was imported from our support forum. The original discussion may contain more detailed answer.

asked Jul 14 '10 at 09:52

xumix's gravatar image

xumix
425717682

edited Aug 26 '10 at 09:00

Dmitri%20Maximov's gravatar image

Dmitri Maximov
22111211

joshua wrote: [[img:xlev53b3]http://habreffect.ru/files/b0c/4a1e71966/Untitled.png[/img:xlev53b3]](http://habreffect.ru/b0c/4a1e71966/Untitled.png)

(Jul 14 '10 at 09:52) Editor Editor's gravatar image

One Answer:

Fixed.

Alexis Kochetov also asked to add the following:

1) You can try to omit most of null checks in case with SQL providers in .Where(predicate) - if predicate is evaluated to NULL, it's the same as false result. But additional unnecessary NULL checks may negatively affect on query plan.

2) If relationship between Person and Fund is 1 <-> 1, you can delcare pairing here, and thus significantly simplify this expression:

private static readonly Expression<Func<Share, string>> PduRegNumberExpression = 
  e => e.FinTool.Person.Fund.PduRegNumber;

Yes, we do this for any boolean expression involving just parameter values and constants (i.e. an expression of boolean type that can be replaced by query parameter) - the feature is called "boolean branching".

See http://blog.alexyakunin.com/2009/12/boo ... tsnet.html

answered Aug 13 '10 at 10:57

Alex%20Yakunin's gravatar image

Alex Yakunin
29714412

So "CAST(1 AS BIT) <> 0" is actually a value of this parameter, that is explicitly injected as string - to ensure query text will be different for its true and false values..

(Aug 13 '10 at 10:57) Alex Yakunin Alex%20Yakunin's gravatar image
Your answer
Please start posting your answer anonymously - your answer will be saved within the current session and published after you log in or create a new account. Please try to give a substantial answer, for discussions, please use comments and please do remember to vote (after you log in)!
toggle preview

powered by OSQA