CODEWITHSUNDEEP
sql.netlinqentity-framework-coreef-core-3.1
cinosz
cinosz

Reputation: 23

LINQ Subquery selects all columns instead of the specified only

I have the following LINQ query:

var query =
       from store in _dbContext.Stores.AsNoTracking()
       join shelf in _dbContext.Shelves.AsNoTracking()
            on store.Id equals shelf.StoreId into storeShelf
       join customers in 
        (
            from customer in _dbContext.Customers.AsNoTracking()
            where customer.IsActive
            select new { customer.Name, customer.shelfId }
        ) on storeShelf.Id equals customers.shelfId into shelfCustomer
       from shelfCustomer2 in shelfCustomer.DefaultIfEmpty()
       select new CompleteModel
       {
           StoreName = store.Name,
           CustomerName = shelfCustomer2.Name
       };

The problem is that the subquery is incorrectly translated. Instead of:

...
SELECT c.Name, c.Age
FROM [dbo].[Customer]
WHERE c.IsActive = 1
...

EF translates to:

...
SELECT c.Id, c.Name, c.Surname, c.Age, c.AddressId, ...
FROM [dbo].[Customer]
WHERE c.IsActive = 1

All columns are selected in the subquery instead of just these two.

Upvotes: 0

Views: 62

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

Even though EF Core 3.x is outdated, it should still handle the following LINQ query correctly:

var query =
    from store in _dbContext.Stores
    join shelf in _dbContext.Shelves on store.Id equals shelf.StoreId
    from shelfCustomer in _dbContext.Customers
        .Where(c => c.shelfId == shelf.Id && c.IsActive)
        .DefaultIfEmpty()
    select new CompleteModel
    {
        StoreName = store.Name,
        CustomerName = shelfCustomer.Name
    };

Please note that I have removed AsNoTracking() because it is redundant when using projections. Additionally, AsNoTracking() only needs to be applied once in a query.

Upvotes: 0

Related Questions