Entity Framework generate not expected query

Question

I have Entity Framework object model with inheritance.

My linq query:

Books.ItemsNoTracking.Where(o => o.Id == new Guid("2D46B66E-64CB-4C99-AF94-5C7414048ECF")).First();

Expected query:

SELECT * FROM  [dbo].[Books] AS [Extent1] 
WHERE cast('2d46b66e-64cb-4c99-af94-5c7414048ecf' as uniqueidentifier) = [Extent1].[Id]

But EF generate:

SELECT 
    [Limit1].[C1] AS [C1], 
    [Limit1].[Id] AS [Id], 
    [Limit1].[Created] AS [Created], 
    [Limit1].[OwnerId] AS [OwnerId], 
    [Limit1].[Deleted] AS [Deleted], 
    [Limit1].[PhysicalUnitId] AS [PhysicalObjectId]
    FROM ( SELECT TOP (1) 
        [Extent1].[Id] AS [Id], 
        [Extent1].[PhysicalObjectId] AS [PhysicalObjectId], 
        [Extent2].[Created] AS [Created], 
        [Extent2].[OwnerId] AS [OwnerId], 
        [Extent2].[Deleted] AS [Deleted], 
        '0X0X' AS [C1]
        FROM  [dbo].[Books] AS [Extent1]
        INNER JOIN [dbo].[Objects] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]
        WHERE cast('2d46b66e-64cb-4c99-af94-5c7414048ecf' as uniqueidentifier) = [Extent1].[Id]
    )  AS [Limit1]

It's most simple example. Other queries are too big and hard to read.

Is there any way to ignore inharitance and create simple query to only one table.

Fabulous · Accepted Answer · 2018-02-17 21:22:40Z

1

In order to get the query that you expect, you should not use the First() extension method. That is what adds the Top 1 clause to the inner query in your example.

I'm not sure which version of Entity Framework you're running but I when I ran similar code on a database I have using EF 6 that comes with Visual Studio, it generated a simple one layer

SELECT TOP 1 <columns> FROM <table> WHERE ID = <passed in parameter>

Since you're searching against an ID field, it should be safe to assume that you expect a single result, in which case you can use either one of Single(), First() or .Take(1) to ensure you have just one record.

You can read more about these here if you wish.

answered Feb 17, 2018 at 21:22

Fabulous

2,4562 gold badges22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework generate not expected query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related