SQL to Linq With Multiple Join, Count and Left Join

SQL to LINQ with multiple join, count and left join

For translating SQL to LINQ query comprehension:

Translate subselects as separately declared variables unless they reference columns outside the subselect, in which case use parentheses to create a sub-query.
Translate each clause in LINQ clause order, translating monadic and aggregate operators (DISTINCT, TOP, MIN, MAX etc) into functions applied to the whole LINQ query.
Use table aliases as range variables. Use column aliases as anonymous type field names.
Use anonymous types (new { ... }) for multiple columns (e.g. in groupby).
Use First().field to get non-key values from the groupby aggregate range variable.
When using EF or EF Core, translate JOIN clauses into navigation properties possibly using .Include().
Otherwise JOIN clauses that are multiple ANDed equality tests between the two tables should be translated into anonymous objects on each side of equals.
JOIN conditions that aren't all equality tests with AND must be handled using where clauses outside the join, or with cross product (from ... from ...) and then where. If you are doing LEFT JOIN, add a lambda Where clause between the join range variable and the DefaultIfEmpty() call.
LEFT JOIN is simulated by using into joinvariable and doing another from the joinvariable followed by .DefaultIfEmpty().
Translate multiple tables in the FROM clause into multiple from clauses.
Translate FROM T1 CROSS APPLY T2 into two from clauses, one for T1 and one for T2.
Translate FROM T1 OUTER APPLY T2 into two from clauses, one for T1 and one for T2, but add .DefaultIfEmpty() to T2.
Replace COALESCE with the conditional operator (?:)and a null test.
Translate IN to .Contains() and NOT IN to !...Contains(), using literal arrays or array variables for constant lists.
Translate x BETWEEN low AND high to low <= x && x <= high.
Translate CASE, ISNULL and IIF to the ternary conditional operator ?:.
SELECT * must be replaced with select range_variable or for joins, an anonymous object containing all the range variables.
SELECT columns must be replaced with select new { ... } creating an anonymous object with all the desired fields or expressions.
References to computed SELECT columns can be translated by repeating the expression or by using let to name the expression before its first use.
Proper FULL OUTER JOIN must be handled with an extension method.
Translate UNION to Concat unless both sub-queries are DISTINCT, in which case you can translate to Union and leave off the DISTINCT.
Translate aggregate queries that have no GROUP BY using a singleton GroupBy: add .GroupBy(r => 1) and then translate the aggregate functions in the Select.
Date Math and some other canonical functions can be accessed using EF.Functions to get an instance of the DbFunctions class (EF Core), EntityFunctions class (EF < 6) or DbFunctions to access the static methods (EntityFramework 6.x).
Translate SQL LIKE expressions using (EF Core >= 2) EF.Functions.Like(column, pattern) or (EF 6.x) DbFunctions.Like(column, pattern).

Applying these rules to your SQL query, you get:

var subrq = from r in Table_R
            group r by r.Id into rg
            select new { Id = rg.Key, cnt = rg.Count() };

var ansq = (from c in Table_C
            join v in Table_V on c.Id equals v.Id
            join r in subrq on c.Id equals r.Id into rj
            from r in rj.DefaultIfEmpty()
            where c.IdUser == "1234"
            group new { c, v, r } by new { c.Id, c.Title, r.cnt } into cvrg
            select new {
                cvrg.Key.Title,
                Nb_V2 = cvrg.Count(),
                Nb_V1 = cvrg.Select(cvr => cvr.v.IdUser).Distinct().Count(),
                Nb_R = (int?)cvrg.Key.cnt
            }).Distinct();

The lambda translation is tricky, but the conversion of LEFT JOIN to GroupJoin...SelectMany is what is needed:

var subr2 = Table_R.GroupBy(r => r.Id).Select(rg => new { Id = rg.Key, cnt = rg.Count() });
var ans2 = Table_C.Where(c => c.IdUser == "1234")
                  .Join(Table_V, c => c.Id, v => v.Id, (c, v) => new { c, v })
                  .GroupJoin(subr, cv => cv.c.Id, r => r.Id, (cv, rj) => new { cv.c, cv.v, rj })
                  .SelectMany(cvrj => cvrj.rj.DefaultIfEmpty(), (cvrj, r) => new { cvrj.c, cvrj.v, r })
                  .GroupBy(cvr => new { cvr.c.Id, cvr.c.Title, cvr.r.cnt })
                  .Select(cvrg => new { cvrg.Key.Title, Nb_V2 = cvrg.Count(), Nb_V1 = cvrg.Select(cvr => cvr.v.IdUser).Distinct().Count(), Nb_R = (int?)cvrg.Key.cnt });

LINQ Left Outer Join Multiple Tables with Group Count and Row Concatenation

Assuming you are using EF, and you have navigation properties set up, then your query would look like this:

var result = context.Parents
  .Select(p => new {
    p.ParentId,
    p.ParentName,
    p.ParentOccupation,
    NumberOfGrandChildren = p.Children
       .SelectMany(c => c.GrandChildren)
       .Count(),
    NamesOfGrandChildren = string.Join(", ", p.Children
      .SelectMany(c => c.GrandChildren)
      .Select(g => g.GrandChildName))
  }).ToList();

Left join and count based on multiple values using linq c#

The problem is that because of the left-join semantics of DefaultIfEmpty(), you always have at least one row. So you need to add a predicate to the Count()

var joined3 = (
    from id1 in joined
    join id2 in tr
       on new { lev = id1.Key, stat = id1.Value } equals new { lev = id2.Level, stat = id2.Status } into grouped
    from id2 in grouped.DefaultIfEmpty()
    group id2 by new {level = id1.Key, status = id1.Value } into grouped
    select new
    {
        level = grouped.Key.level,
        status = grouped.Key.status,
        count =  grouped.Count(id2 => id2.Key != null)
    }).ToList();

Alternatively, a simpler method is: don't group, but instead use a correlated count of the other list

var joined3 = (
    from id1 in joined
    select new
    {
        level = id1.level,
        status = id1.status,
        count =  tr.Count(id2 => id2.Key == id1.Key && id2.Value == id1.Value)
    }).ToList();

I see no reason to use ToString here, and it is likely to impact performance. Key and Value should be the same type on each list/table respectively.

LINQ to SQL LEFT JOIN, GROUP BY, COUNT, MAX and subquery

I'm not sure why you modified your LINQ query so much from the original SQL query, but translating the SQL query closer to literally using the rules from my SQL to LINQ Recipe I get

var MaxPriceInPLN = db.Bids.Max(b => b.PriceInPLN);
var ans = from U in db.Users
          where !U.Roles.Any(r => r.RoleId == adminRoleId)
          join b in db.Bids on U.Id equals b.ApplicationUserId into bj
          from b in bj.DefaultIfEmpty()
          group new { U, b } by new { U.Id, U.UserName, U.Email } into Ubg
          let maxBidPriceInPLN = Ubg.Max(Ub => Ub.b.PriceInPLN)
          select new {
              Ubg.Key.Id,
              Ubg.Key.UserName,
              Ubg.Key.Email,
              PriceInPLN = maxBidPriceInPLN ?? 0,
              BidsCount = Ubg.Count(Ub => Ub.b != null),
              IsMax = maxBidPriceInPLN == MaxPriceInPLN
          };

You can simplify the LINQ query by using the EF navigation properties to hide the join from your query:

var MaxPriceInPLN = db.Bids.Max(b => b.PriceInPLN);
var ans = from U in db.Users
          where !U.Roles.Any(r => r.RoleId == adminRoleId)
          let maxBidPriceInPLN = U.Bids.Max(b => b.PriceInPLN)
          select new {
              U.Id,
              U.UserName,
              U.Email,
              PriceInPLN = maxBidPriceInPLN ?? 0,
              BidsCount = U.Bids.Count(),
              IsMax = maxBidPriceInPLN == MaxPriceInPLN
          };

LINQ version of sql query with multiple left joins, group by, and datetime conversion

I believe this should work, barring issues with DateTimeOffset.Date and timezones.

Since EF Core 3 only has extremely limited support for translating GroupJoin (basically just to LEFT JOIN), you must split the query into two parts, a SQL query with LEFT JOIN and then a client side GroupBy to create the effect of GroupJoin.

var utcNowDate = DateTimeOffset.UtcNow.Date;

var dbQuery = from p in _context.Collector_Profile
              where p.ID == CollectorID
              join s in _context.ClaimStatusHistory.Where(s => s.StatusDateTZ.Value.Date == utcNowDate) on p.AppUserID equals s.AppUserID into gs
              from s in gs.DefaultIfEmpty()
              join c in _context.Claim_Notes.Where(c => c.PostDateTZ.Value.Date == utcNowDate) on p.ID equals c.CollectorID into gc
              from c in gc.DefaultIfEmpty()
              select new { p.DayGoal, s = s.ClaimStatusHistoryID, c = c.ID };

var query = from psc in dbQuery.AsEnumerable()
            group new { psc.s, psc.c } by psc.DayGoal into grouped
            select new UserStatistics {
                DayGoal = grouped.Key,
                NoteCount = grouped.Count(sc => sc.c != null),
                ActionCount = grouped.Count(sc => sc.s != null)
            };

return query.FirstOrDefault();

Conditional counting in Linq's Group by multiple columns with left join

I am not sure how helpful this will be because it is a translation of your SQL, and your LINQ doesn't seem to be related to your SQL, but I have:

var ans = from p in db.JDE_Processes
          join uuu in db.JDE_Users on p.FinishedBy equals uuu.UserId into uuuj
          from uuu in uuuj.DefaultIfEmpty()
          join h in db.JDE_Handlings on p.ProcessId equals h.ProcessId into hj
          from h in hj
          group new { p, h } by new { p.ProcessId, p.Description, p.StartedOn, p.StartedBy, p.FinishedOn, p.FinishedBy, p.PlannedFinish, p.PlannedStart } into phg
          select new {
              phg.Key.ProcessId,
              phg.Key.Description,
              phg.Key.StartedOn,
              phg.Key.StartedBy,
              phg.Key.FinishedOn,
              phg.Key.FinishedBy,
              phg.Key.PlannedFinish,
              phg.Key.PlannedStart,
              HandlingStatus = phg.Where(ph => ph.h.IsCompleted == null).Count()
          };

How can I code a LINQ query with a count and a left join?

like the first hit off SO search on google

from p in context.ParentTable
join c in context.ChildTable on p.ParentId equals c.ChildParentId into j1
from j2 in j1.DefaultIfEmpty()
group j2 by p.ParentId into grouped
select new { ParentId = grouped.Key, Count = grouped.Count(t=>t.ChildId != null) }

soo something like

from d in decksource
join de in deck on d.deckguid equals de.ChildParentId into j1
from j2 in j1.DefaultIfEmpty()
group j2 by d.deckguid into grouped
select new { 
    deckguid = grouped.Key,
    Count = grouped.Count(t=>t.deckguid != null) 
}

soo

var res = (from d in App.EF.Db2.Decksource
        join de in App.EF.Db2.Deck on d.deckguid equals de.ChildParentId into j1
        from j2 in j1.DefaultIfEmpty()
        group j2 by d.deckguid into grouped
        select new { 
            deckguid = grouped.Key,
            Count = grouped.Count(t=>t.deckguid != null) 
        }).ToList()

LINQ to SQL with multiple joins, group by and count

For Left Joins - I suggest using a from with a where and a DefaultIfEmpty
You need to group using an anonymous type in order to group multiple parameters

Value cannot be null. Parameter name: inner

Are any of joining properties nullable?

 var qry = 
        from Cust in tblCust
        from Order in tblOrder.Where(x => Cust.CustID == x.CustID)
                              .DefaultIfEmpty()
        from Item in tblItem.Where(x => Order.OrderID == x.OrderID)
                            .DefaultIfEmpty()
        group new { Cust, Order.OrderId, Item.ItemId } by new { Cust.CustID, Cust.CustName } into grp
        let numItems = grp.Select(x => x.ItemId).Distinct().Count()
        orderby numItems 
        select new
        {
            ID = grp.Key.CustID,
            Name = grp.Key.CustName,
            numOrders = grp.Select(x => x.OrderId).Distinct().Count(),
            numItems,
        };

Using group by left join and count using linq

I would write the SQL query like this:

SELECT
      p.Post_ID
    , p.Post
    , COUNT(c.CommentID) as cmtcount
FROM Posts p
    LEFT JOIN Comments c ON c.PostID = p.Post_ID
GROUP BY p.Post_ID, p.Post

In LinQ, you could write something like this:

from p in db.Posts
join c in db.Comments on p.Post_ID equals c.PostID into commentsInPost
select new
{
    PostId = p.Post_ID,
    Post = p.Post1,
    cmtcount = commentsInPost.Count()
};

The SQL generated by LinQ is as follows:

SELECT 
    [Extent1].[Post_ID] AS [Post_ID], 
    [Extent1].[Post] AS [Post], 
    (SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Comments] AS [Extent2]
        WHERE [Extent1].[Post_ID] = [Extent2].[PostID]) AS [C1]
FROM [dbo].[Posts] AS [Extent1]

Both SQL syntax are correct and will return the same result, it is up to you to decide which one to use.

SQL to Linq With Multiple Join, Count and Left Join