Question 85994 in ASP.NET Question posted in
The official documentation can be found here.

Entity Framework Linq Query to return list of IDs – Asp.net

How do I get a list of companyIDs who shipped all their orders in linq?
Scenario: Each company got a bunch of customers and orders for customers. Here from the below tables, CompanyB and CompanyC has shipped all the orders to customers and I want to get those ID from a linq query.

3 tables involved Company, Customers and Orders

Company
CompanyID CompanyName
   1         CompanyA
   2         CompnayB
   3         CompnayC

Customers
CustomerID CustomerName   CompanyID
   1         CustomerA      1
   2         CustomerB      2
   3         CustomerC      2
   4         CustomerD      2
   5         CustomerE      1
   6         CustomerF      2
   7         CustomerG      3


Orders
OrderID  CustomerID  Shipped
  1         2           1
  2         3           1
  3         4           1
  4         6           1
  5         1           0
  6         7           1

Here is what I tried and I’m not getting the right output

var IDs = Company.Where(co => co.Customers.Any(cu => cu.Orders.Any(o => o.shipped))).ToList();

Any help is much appreciated..

Tags:

4

Answers


  1. 0

    Try this:

        var IDs =  (from co in Company
             join cu in Customers on co.CompanyID equals cu.CompanyID
             join o in Orders on cu.CustomerID  equals o.CustomerID
             where o.Shipped
             select new { co.CompanyID }).ToList();
    Login or Signup to reply.
  2. 0

    I haven’t tried running this, but what if I try All instead of Any? Probably, if you are working with a database on EF and not just a collection, you should have lazy loading enabled.
    And, since you only need the ID, I added Select

    var IDs = Company
    .Where(co => co.Customers.All(cu => cu.Orders.All(o => o.Shipped)))
    .Select(co => co.CompanyID)
    .ToList();
    Login or Signup to reply.
  3. 0

    you can join two tables with linq like below :

    var Ids = (from cu in Customers
          join o in Orders on cu.CustomerId equals o.CustomerId
          where o.Shipped
          select cu.CompanyId).Distinct().ToList();

    and you can use fluentApi :

    var ids = Orders.Where(o => o.Shipped).Join(Customers, o => o.CustomerId, c => c.CustomerId, (o, c) => new
{
    c.CompanyId
}).Distinct().ToList();
    Login or Signup to reply.
  4. 0

    You can rely on SelectMany as an alternative to double All calls:

    var companyIds = Companies
    .Where(co => co.Customers.SelectMany(c => c.Orders).All(o => o.shipped))
    .Select(co => co.CompanyId)
    .ToList();

    This feels slightly more semantic IMHO for your requirement.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search