Nested Query Entity Framework

Question

Need help in converting below SQL nested query to a LINQ query?

select P.ProductId, P.Name, C.Name, I.Image
from Product P 
join ProductImage I on P.ProductId = I.ProductId
join ProductCategory C on P.Category = C.CategoryId
where P.ProductId in (select distinct ProductId
                      from ProductVariantMapping M
                      where M.GUID in (select top 3 V.Guid
                                       from [Order] O 
                                       join Inventory V on V.InventoryId = O.InventoryId
                                       group by O.InventoryId, V.Guid
                                       order by Sum(O.Quantity) desc))

Below is my attempt in converting to LINQ query :

var a = (from product in ekartEntities.Products
         join productImage in ekartEntities.ProductImages
                        on product.ProductId equals productImage.ProductId
         join category in ekartEntities.ProductCategories
                        on product.Category equals category.CategoryId
         where product.ProductId
         select new ProductDTO()
                    {
                            ProductId = product.ProductId,
                            Name = product.Name,
                            Category = category.Name,
                            Image = productImage.Image
                    }).ToList();

what is the equivalent of "IN" when converting to LINQ .

I got the solution for 'IN' clause. But how do I use sum(Quantity) in order by after grouping?

I am new to Entity Framework. Can anyone help me?

What looks like your linq query, have you try anything?

Emre Savcı
– Emre Savcı

2018-09-08 07:49:26 +00:00
Commented Sep 8, 2018 at 7:49 — Emre Savcı
– Emre Savcı, Commented Sep 8, 2018 at 7:49
@EmreSavcı I have posted it above. Can't you see?

Suyash Gupta
– Suyash Gupta

2018-09-08 07:50:54 +00:00
Commented Sep 8, 2018 at 7:50 — Suyash Gupta
– Suyash Gupta, Commented Sep 8, 2018 at 7:50
Sorry, I see now. What about your linq?

Emre Savcı
– Emre Savcı

2018-09-08 07:52:49 +00:00
Commented Sep 8, 2018 at 7:52 — Emre Savcı
– Emre Savcı, Commented Sep 8, 2018 at 7:52
@EmreSavcı Updated my try of the LINQ query.

Suyash Gupta
– Suyash Gupta

2018-09-08 08:02:45 +00:00
Commented Sep 8, 2018 at 8:02 — Suyash Gupta
– Suyash Gupta, Commented Sep 8, 2018 at 8:02

guner · Accepted Answer · 2018-09-08 09:19:57Z

3

In LINQ, you will need to use the "contains()" method to generate the 'IN' You need to put a list in the Contains method. If sends a query, that query will be repeated for completions and this will lead to performance loss.

Sample:

var sampleList = (from order ekartEntities.Order
                  join inventory in ekartEntities.Inventory on order.InventoryId equals inventory.InventoryId
                  select order).toList();

var query = (from product in ekartEntities.Products
                        join productImage in ekartEntities.ProductImages
                        on product.ProductId equals productImage.ProductId
                        join category in ekartEntities.ProductCategories
                        on product.Category equals category.CategoryId
                     where sampleList.Contains(product.ProductId)
                        select new ProductDTO()
                        {
                            ProductId = product.ProductId,
                            Name = product.Name,
                            Category = category.Name,
                            Image = productImage.Image
                        }).ToList();

edited Sep 8, 2018 at 9:19

answered Sep 8, 2018 at 9:14

guner

462 bronze badges

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

3 Comments

Suyash Gupta Over a year ago

Thank you so much for your help. Can you also tell me how do I use orderby Sum(Quantity) after grouping them?

guner Over a year ago

I'm sorry it was late. var x = (from ... group orders by new {InventoryId = order.InventoryId, Guid = order.Guid, Quantity = order.Quantity} into orderGrp orderby order.Key.Quantity select new { bla bla })

Suyash Gupta Over a year ago

I needed sum(Quantity), not orderby just "Quantity". Anyways, I got the solution. Thanks, anyways :)

Daniel · Accepted Answer · 2021-03-23 06:58:17Z

Do not apply ToList() in the first query

I made some test.

Test 1 (with my own data):

    var phIds = new List<string>
    {
        //List of Ids
    };
    using (var db = new ApplicationDbContext())
    {
        var studentsId = db.Relations
            .Where(x => phIds.Contains(x.RelationId))
            .Select(x => x.Id)
            .Distinct(); //IQueryable here
        var studentsQuery = from p in db.Students
            where studentsId.Contains(p.Id)
            select p;

        var students= studentsQuery .ToList();
    }

The generated query looks like :

SELECT 
    [Extent1].[Id] AS [Id], 
    [...]
    FROM [dbo].[Students] AS [Extent1]
    WHERE  EXISTS (SELECT 
        1 AS [C1]
        FROM ( SELECT DISTINCT 
            [Extent2].[StudentId] AS [StudentId]
            FROM [dbo].[Relations] AS [Extent2]
            WHERE ([Extent2].[RelationId] IN (N'ccd31c3d-dfa3-4b40-...', N'd2cb05a2-ece3-4060-...'))
        )  AS [Distinct1]
        WHERE [Distinct1].[StudentId] = [Extent1].[Id]
    )

The query looks exactly like I wanted

However, if you add the ToList() in the first query to get the ids, you no longer have an IQueryable but a list.

Test 2 : wrong (I added ToList):

var phIds = new List<string>
{
    //List of Ids
};
using (var db = new ApplicationDbContext())
{
    var studentsId = db.Relations
        .Where(x => phIds.Contains(x.RelationId))
        .Select(x => x.Id)
        .Distinct().ToList(); // No longer IQueryable but a list of 3000 int
    var studentsQuery = from p in db.Students
        where studentsId .Contains(p.Id)
        select p;

    var students= studentsQuery .ToList();
}

The generated query is ugly:

SELECT 
    [Extent1].[Id] AS [Id], 
    [...]
    FROM [dbo].[Patients] AS [Extent1]
    WHERE [Extent1].[Id] IN (611661, 611662, 611663, 611664,.... 
    //more than 3000 ids here
    )

Collectives™ on Stack Overflow

Nested Query Entity Framework

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related