none
LINQ and Entity Framework query problem RRS feed

  • Question

  • Following Linq to Entities query is causing the "Unable to create a constant value of type 'Data.InhouseUnit'. Only primitive types ('such as Int32, String, and Guid') are supported in this context" exception.

    IList<FaultReport> faultReports = (from fr in _session.FaultReports
                                                   where fr.CreatedOn > dateTime
                                                   select new FaultReport
                                                   {
                                                       Id = fr.Id,
                                                       ExecutionDate = fr.ExecutionDate ?? DateTime.MinValue,
                                                       FaultType = fr.FaultType,
                                                       Quarters = fr.Quarters,
                                                       InhouseSpaceId = fr.InhouseSpaceId,
                                                       InhouseSpace = new InhouseSpace { Id = fr.InhouseSpace.Id, Name = fr.InhouseSpace.Name },
                                                       InhouseUnitId = fr.InhouseUnitId ?? Guid.Empty,
                                                       InhouseUnit = fr.InhouseUnitId == Guid.Empty ? null : new InhouseUnit { Id = fr.InhouseUnit.Id, Name = fr.InhouseUnit.Name }
    						}).ToList();
    

    Specifically, it is the if expression in bold font which causes the exception. I need to make the check as fr.InhouseUnitId is a nullable. If I take out the the bolded expression, the rest of the statement works just fine. I have spent a fair amount of time, in msdn forum and on web, to understand what is causing the exception but still cannot quite understand. Guid is scalar so it should work, right? Even this expression InhouseUnit = true ? nullnew InhouseUnit() in place of the bolded expression in the above statement wouldn't work. Can we even write if/else

    If i try to write an extension method to take away the logic and just return a result, following exception is thrown:

    LINQ to Entities does not recognize the method 'System.Object GuidConversion(System.Nullable`1[System.Guid], System.Object)' method, and this method cannot be translated into a store expression

    Any ideas where am i wrong.

    Thx


    • Edited by Xience Wednesday, January 4, 2012 8:43 PM
    Wednesday, January 4, 2012 3:55 PM

Answers

  • Hi Xience,

    With your schema I was able to reproduce the error. It seems L2E won't allow you to conditionally instantiate an object in that fashion. I was able to "trick" EF into giving you what you want, although it's not the most elegant solution.

    IList<FaultReport> faultReports = (from fr in _session.FaultReports
                                                       where fr.CreatedOn > dateTime
                                                       select new FaultReport
                                                       {
                                                           Id = fr.Id,
                                                           ExecutionDate = fr.ExecutionDate ?? DateTime.MinValue,
                                                           FaultType = fr.FaultType,
                                                           Quarters = fr.Quarters,
                                                           InhouseSpaceId = fr.InhouseSpaceId,
                                                           InhouseSpace = new InhouseSpace
                                                                              {
                                                                                  Id = fr.InhouseSpace.Id, 
                                                                                  Name = fr.InhouseSpace.Name
                                                                              },
                                                           InhouseUnitId = fr.InhouseUnitId ?? Guid.Empty,
                                                           InhouseUnit = _session.InhouseUnits.Where(iu => iu.Id == fr.InhouseUnitId)
                                                                                        .Select(iu => new InhouseUnit
                                                                                                       {
                                                                                                           Id= iu.Id,
                                                                                                           Name=iu.Name,
                                                                                                       }).FirstOrDefault(),
                                                       }).ToList();


    Other than that I think the only other possibility would be to remove the condition and then enumerate the collection and set InhouseUnit to null for the items which have InhouseUnitId == Guid.Empty.

    Regards,

    Tyler


    • Edited by Tyler_A Wednesday, January 11, 2012 12:57 AM
    • Proposed as answer by Allen_MSDNModerator Wednesday, January 11, 2012 3:27 AM
    • Marked as answer by Xience Wednesday, January 11, 2012 11:17 PM
    Wednesday, January 11, 2012 12:56 AM

All replies

  • Hi Xience,

     Welcome to MSDN Forum.

    I'm doing research on this issue, and I'll come back as soon as possible.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Sunday, January 8, 2012 1:56 PM
    Moderator
  • Hi Xience,

    Is the code compiled fine? If the error is thrown at runtime, I think it may because the query statement can't be translated to T-SQL. Entity Framework knows nothing about your InhouseUnit entity. It is a known issue about Linq to Entities, please refer to the link below.

    http://msdn.microsoft.com/en-us/library/bb896317.aspx

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Monday, January 9, 2012 3:38 AM
    Moderator
  • Hi Allen,

    Yes, the code compiles fine and the error is thrown at runtime. I agree to the part that the query is not getting translated to T-SQL but if entity framework fails to recognize InhouseUnit entity then by that count, it should also fail to recognize InhouseSpace entity which it does not.

    If i instead replace the bolded statement, in my post, with InhouseUnit = new InhouseUnit { Id = fr.InhouseUnit.Id, Name = fr.InhouseUnit.Name } then everything works fine, so i conclude it has to be something wrong with the if/else expression.

    The known issues page does not seem to list any issue alluding to Linq to Entities failing to recognize entities or perhaps i just failed to see it. Anyway, I very much appreciate your help.

    Thx,

    Xience

    Monday, January 9, 2012 10:17 AM
  • Hi Xience,

    Could you please post your table schema here? I want to reproduce the scenario, but I don't know what the FaultReport is, is it a Data Transfer Object? If it is, how do you query out data from context.FaultReports? If it is an entity which mapped to database, it is obvious that we can't create a new FaultReport instance in the Linq query statement.

    I assume the 'FaultReport' is a DTO, below is my test code

    class TempClass
        {
            public Team team { get; set; }
            public Player player { get; set; }
        }
    
    class Program
        {
            static void Main(string[] args)
            {
                using (NBAEntities1 context = new NBAEntities1())
                {
                    IList<TempClass> list = (from t in context.Players
                                             where t.ID > 1
                                             select new TempClass
                                             {
                                                 team = t.Team.Name == "Lakers" ?
                                                 t.Team : null,
                                                 player = t
                                             }).ToList();
                }
                
            }
        }

    It works well.

    Best Regards 


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, January 10, 2012 5:55 AM
    Moderator
  • Hi Allen,

    Thanks for replying. I have a table by the name "FaultReports" and then a DTO by the name "FaultReport", as you guessed it right. I map all properties from context.FaultReports to my FaultReport DTO object in query. The problem is that [dbo].[FaultReports].[InhouseUnitId] which is a foreign key can be null and based on whether null or not i need to create objects and assign to my DTO properties. Same is the case for InhouseUnit, there is a table and a DTO.

    I have also tested if condition in the following statement with these alternatives (fr.InhouseUnitId == null), (fr.InhouseUnitId == new Nullable<Guid>) but still it breaks at runtime.

    InhouseUnit = fr.InhouseUnitId == Guid.Empty ? null : new InhouseUnit { Id = fr.InhouseUnit.Id, Name = fr.InhouseUnit.Name }

    Table schema:

    CREATE TABLE [dbo].[FaultReports](
    	[Id] [uniqueidentifier] NOT NULL,
    	[ExecutionDate] [datetime] NULL,
    	[FaultType] [nvarchar](50) NULL,
    	[DistrictId] [uniqueidentifier] NULL,
    	[AreaId] [uniqueidentifier] NULL,
    	[Quarters] [nvarchar](50) NULL,
    	[ExecutionerId] [uniqueidentifier] NULL,
    	[CustomerId] [uniqueidentifier] NULL,
    	[InhouseSpaceId] [uniqueidentifier] NOT NULL,
    	[InhouseUnitId] [uniqueidentifier] NULL,
    	[Ao_fras] [nvarchar](50) NULL,
    	[Solution] [nvarchar](50) NULL,
    	[Comments] [nvarchar](100) NULL,
    	[Remarks] [nvarchar](100) NULL,
    	[FaultDescription] [ntext] NULL,
    	[ContactWayId] [uniqueidentifier] NULL,
    	[CreatedOn] [datetime] NOT NULL,
    	[CreatedByUserId] [uniqueidentifier] NOT NULL,
    	[IsRecordProcessed] [bit] NOT NULL,
     CONSTRAINT [PK_FaultReports] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    ALTER TABLE [dbo].[FaultReports]  WITH CHECK ADD  CONSTRAINT [FK_FaultReports_Areas] FOREIGN KEY([AreaId])
    REFERENCES [dbo].[Areas] ([Id])
    GO
    
    ALTER TABLE [dbo].[FaultReports] CHECK CONSTRAINT [FK_FaultReports_Areas]
    GO
    
    ALTER TABLE [dbo].[FaultReports]  WITH CHECK ADD  CONSTRAINT [FK_FaultReports_ContactWays] FOREIGN KEY([ContactWayId])
    REFERENCES [dbo].[ContactWays] ([Id])
    GO
    
    ALTER TABLE [dbo].[FaultReports] CHECK CONSTRAINT [FK_FaultReports_ContactWays]
    GO
    
    ALTER TABLE [dbo].[FaultReports]  WITH CHECK ADD  CONSTRAINT [FK_FaultReports_Customers] FOREIGN KEY([CustomerId])
    REFERENCES [dbo].[Customers] ([Id])
    GO
    
    ALTER TABLE [dbo].[FaultReports] CHECK CONSTRAINT [FK_FaultReports_Customers]
    GO
    
    ALTER TABLE [dbo].[FaultReports]  WITH CHECK ADD  CONSTRAINT [FK_FaultReports_Districts] FOREIGN KEY([DistrictId])
    REFERENCES [dbo].[Districts] ([Id])
    GO
    
    ALTER TABLE [dbo].[FaultReports] CHECK CONSTRAINT [FK_FaultReports_Districts]
    GO
    
    ALTER TABLE [dbo].[FaultReports]  WITH CHECK ADD  CONSTRAINT [FK_FaultReports_Executioners] FOREIGN KEY([ExecutionerId])
    REFERENCES [dbo].[Executioners] ([Id])
    GO
    
    ALTER TABLE [dbo].[FaultReports] CHECK CONSTRAINT [FK_FaultReports_Executioners]
    GO
    
    ALTER TABLE [dbo].[FaultReports]  WITH CHECK ADD  CONSTRAINT [FK_FaultReports_InhouseSpace] FOREIGN KEY([InhouseSpaceId])
    REFERENCES [dbo].[InhouseSpace] ([Id])
    GO
    
    ALTER TABLE [dbo].[FaultReports] CHECK CONSTRAINT [FK_FaultReports_InhouseSpace]
    GO
    
    ALTER TABLE [dbo].[FaultReports]  WITH CHECK ADD  CONSTRAINT [FK_FaultReports_InhouseUnit] FOREIGN KEY([InhouseUnitId])
    REFERENCES [dbo].[InhouseUnit] ([Id])
    GO
    
    ALTER TABLE [dbo].[FaultReports] CHECK CONSTRAINT [FK_FaultReports_InhouseUnit]
    GO
    
    ALTER TABLE [dbo].[FaultReports]  WITH CHECK ADD  CONSTRAINT [FK_FaultReports_Users] FOREIGN KEY([CreatedByUserId])
    REFERENCES [dbo].[Users] ([Id])
    GO
    
    ALTER TABLE [dbo].[FaultReports] CHECK CONSTRAINT [FK_FaultReports_Users]
    GO
    

    Tuesday, January 10, 2012 1:26 PM
  • Hi Xience,

    With your schema I was able to reproduce the error. It seems L2E won't allow you to conditionally instantiate an object in that fashion. I was able to "trick" EF into giving you what you want, although it's not the most elegant solution.

    IList<FaultReport> faultReports = (from fr in _session.FaultReports
                                                       where fr.CreatedOn > dateTime
                                                       select new FaultReport
                                                       {
                                                           Id = fr.Id,
                                                           ExecutionDate = fr.ExecutionDate ?? DateTime.MinValue,
                                                           FaultType = fr.FaultType,
                                                           Quarters = fr.Quarters,
                                                           InhouseSpaceId = fr.InhouseSpaceId,
                                                           InhouseSpace = new InhouseSpace
                                                                              {
                                                                                  Id = fr.InhouseSpace.Id, 
                                                                                  Name = fr.InhouseSpace.Name
                                                                              },
                                                           InhouseUnitId = fr.InhouseUnitId ?? Guid.Empty,
                                                           InhouseUnit = _session.InhouseUnits.Where(iu => iu.Id == fr.InhouseUnitId)
                                                                                        .Select(iu => new InhouseUnit
                                                                                                       {
                                                                                                           Id= iu.Id,
                                                                                                           Name=iu.Name,
                                                                                                       }).FirstOrDefault(),
                                                       }).ToList();


    Other than that I think the only other possibility would be to remove the condition and then enumerate the collection and set InhouseUnit to null for the items which have InhouseUnitId == Guid.Empty.

    Regards,

    Tyler


    • Edited by Tyler_A Wednesday, January 11, 2012 12:57 AM
    • Proposed as answer by Allen_MSDNModerator Wednesday, January 11, 2012 3:27 AM
    • Marked as answer by Xience Wednesday, January 11, 2012 11:17 PM
    Wednesday, January 11, 2012 12:56 AM
  • Hi Xience,

    I've create the table with your script and reproduce the scenario, I also test @Tyler_A's code, it's a good workaround. I'm not sure what the root cuase to the issue, you can take Tyler_A's code as the solution. If I find out the cause, I'll let you know.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Wednesday, January 11, 2012 3:26 AM
    Moderator
  • Thanks Tyler and Allen, i can take Tyler's workaround as a solution for now.
    Wednesday, January 11, 2012 11:17 PM