How to Do a Subquery in Linq

How to do a subquery in LINQ?

Here's a subquery for you!

List<int> IdsToFind = new List<int>() {2, 3, 4};

db.Users
.Where(u => SqlMethods.Like(u.LastName, "%fra%"))
.Where(u =>
    db.CompanyRolesToUsers
    .Where(crtu => IdsToFind.Contains(crtu.CompanyRoleId))
    .Select(crtu =>  crtu.UserId)
    .Contains(u.Id)
)

---

Regarding this portion of the question:

predicateAnd = predicateAnd.And(c => c.LastName.Contains(
                                TextBoxLastName.Text.Trim()));

I strongly recommend extracting the string from the textbox before authoring the query.

string searchString = TextBoxLastName.Text.Trim();
predicateAnd = predicateAnd.And(c => c.LastName.Contains( searchString));

You want to maintain good control over what gets sent to the database. In the original code, one possible reading is that an untrimmed string gets sent into the database for trimming - which is not good work for the database to be doing.

How to JOIN a Sub-Query using LINQ

You can do it the exact same way as in SQL. _db.ProductionOrders in LINQ to Entities represents the dbo.ProductionOrders in SQL, and you can use it in both top and subquery level:

var query = _db.ProductionOrders
    .Join(_db.ProductionOrders
        .GroupBy(p2 => new { p2.OrderNumber })
        .Select(g => new { g.Key.OrderNumber, Timestamp = g.Max(e => e.Timestamp) }), // subquery
        p1 => new { p1.OrderNumber, p1.Timestamp }, p2 => p2, // join condition
        (p1, p2) => p1) // result selector (p1.*)
    .Where(p1 => p1.Material == 42);

(Note: Not sure where Label is coming from in your SQL queries, so using OrderNumber instead).

or if you prefer, the same in LINQ query syntax:

var query =
    from p1 in _db.ProductionOrders
    join p2 in (from p2 in _db.ProductionOrders
                group p2 by new { p2.OrderNumber }) into g
                select new { g.Key.OrderNumber, Timestamp = g.Max(p2 => p2.Timestamp) })
    on new { p1.OrderNumber, p1.Timestamp } equals p2
    where p1.Material == 42
    select p1;

Simple Example Subquery Linq

Simple answer is use the "let" keyword and generate a sub-query that supports your conditional set for the main entity.

var usersEnrolledInCourses = from u in User_Users
                                 let ces = from ce in Course_Enrollments
                                           select ce.UserID
                                 where ces.Contains(u.UserID)
                             select u;   

This will create an exists block in TSQL similar to

SELECT [Extent1].*
   FROM dbo.User_Users AS Extent1
   WHERE EXISTS (SELECT 1 AS [C1]
                     FROM dbo.Course_Enrollements AS Extent2
                     WHERE (Extent2.UserID = Extent1.UserId))

It's close to what you've asked for and will generally create the same query plan on SQL Server.

Hope this helps!

Subquery in select Linq

Compiler doesn't know your subquery will always return one row, since the return type is still IEnumerable (or IQueryable). This can be solved by using First() or Single() (or the corresponding ..OrDefault() version), however, it is more common to just use Count() without grouping, for example :

Com = (from cr1 in cumulativeresult
       where cr1.Days < cr.Days
       select cr1
      ).Count() + cr.Com,

You might also want to consider moving the subquery into a let clause, so it can be reused to calculate Com and ComPercent :

var result = from cr in cumulativeresult
             let totalCount = 
                      (from cr1 in cumulativeresult
                       where cr1.Days < cr.Days
                       select cr1
                      ).Count() + cr.Com 
             orderby cr.Days
             select new
             {
                Days = cr.Days,
                Com = totalCount,
                ComPercent = Math.Round((double)totalCount / ComCount * 100, 2)
             };

LINQ subquery IN

var innerquery = from x in context.DataDictVal
                 where x.TbiTableName == myTableNameVariable
                    && x.DdColumnName == "Status"
                    && x.DdbColumnValLangDsc1.StartsWith("ac")
                 select x.DdvColumnVal;

var query = from c in context.Client
            where innerquery.Contains(c.Etat)
            select c;

How do I write a Linq query that needs a subquery?

You could improve readability:

var tickets = cxt.SupportTickets
  .Select(t => new { 
       Ticket = t, 
       CoverItem = t.System.CoverItems.FirstOrDefault(ci => // some query)
    })
  .Select(x => new {
    ID = x.Ticket.ID,
    Customer = x.Ticket.Customer.Name,
    CoverLevel = x.CoverItem?.Level.Name,
    Expiry = x.CoverItem?.Expiry.ToLongDateString()
  });

sql query do in subquery in LINQ

In Linq you seldom need joins. With a proper database design with relations established, Linq navigation properties does the job (which are generated automatically by the tools for you):

var list = from p in _DbContext.Products
           select new {
              p.Name,
              p.DisplayName,
              Count = p.LicenseActivations.Count(x => x.AccountId=="QWNjb3VudDo2N2YyMTcwMC0xMWZlLTExZWItYjNkMS0yN2U1Mjk0MGVhYmU=")
           };

is the corresponding Linq query of your SQL (ToList() is optional).

EDIT: If you don't have proper relations in your database, then:

var list = from p in _DbContext.Products
           select new {
                  p.Name,
                  p.DisplayName,
                  Count = _DbContext.LicenseActivations
         .Count(x => x.ProductId == p.Id &&
   x.AccountId=="QWNjb3VudDo2N2YyMTcwMC0xMWZlLTExZWItYjNkMS0yN2U1Mjk0MGVhYmU=")
               };

How to use GroupBy() in subqueries with LINQ?

Error message says that you have to include Id column in projection. But you can't do that with GroupBy. So rewrite query into two steps (removed not needed includes):

var rawRecords = UnitOfWork.FreeBets
    .Where(f => f.FreeBetCards.Any(cards => cards.UserId == request.UserId))
    .Select(f => new 
    {
        FreeBetId = f.FreeBetId
        LineCategories = f.FreeBetCategories.Select(c => new { c.Id, c.LineCategoryID, c.Title })
            .ToList()
    })
    .AsEnumerable();

var records = rawRecords
    .Select(f => new FreeBetDTO
    {
        FreeBetId = f.FreeBetId
        LineCategories = f.LineCategories.GroupBy(g => new { g.LineCategoryID, g.Title })
            .Select(c =>
                new LineCategoryDTO
                {
                    LineCategoryID = c.Key.LineCategoryID,
                    Title = c.Key.Title
                })
    });

Similar query, but more optimal:

var query =
    from f in UnitOfWork.FreeBets
    from c in f.FreeBetCards
    where f.FreeBetCards.Any(cards => cards.UserId == request.UserId)
    select new { f.FreeBetId, c.LineCategoryID, c.Title };

query = query.Distinct();

var records = query.AsEnumerable()
    .GroupBy(f => f.FreeBetId)
    .Select(g => new FreeBetDTO
    {
        FreeBetId = g.Key
        LineCategories = g.Select(c =>
                new LineCategoryDTO
                {
                    LineCategoryID = c.LineCategoryID,
                    Title = c.Title
                })
            .AsEnumerable()
    });

---

Related Topics