0

I am trying to convert the following query into a LINQ statement.

select u.code, u.id, count(d.unitid) from Unit u
Left join Package d on u.id= d.unitid and d.sstatus = 'Open'
where d.hunit is not null
group by u.code, u.id

This is what I have,

var result = 
    from U in Table<Unit>().ToList()
    join D in Table<Package>().Where(x => x.status == "Open").ToList() on U.Id equals D.UnitId into def1
    from def2 in def1.DefaultIfEmpty()
    group def2 by new
    {
        U.Id,
        U.code
    } into grouped
    select new
    {
        Hmy = grouped.Key.Id,
        Code = grouped.Key.Code,
        TotalPAckages = grouped.Count()
    };

But, in above code, I am also getting the results with UnitId is null in Package table. I am not sure where and how to apply the Where clause part (where d.hunit is not null) in the above LINQ statement.

Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
Marcus25
  • 863
  • 1
  • 15
  • 31
  • 1
    After second `from`, add `where def2?.hunit != null`. The reason why you get `UnitId` is null as you do LEFT JOIN `Unit` table with `Package` table. To avoid this result, possibly you need revise your query and change it to INNER JOIN rather than LEFT JOIN. – Yong Shun Apr 13 '22 at 06:56
  • 3
    Whet ORM is this? – Caius Jard Apr 13 '22 at 07:13
  • 2
    What is the point to put `.ToList()` in both recordsets? Do you understand that you have loaded almost full tables into the memory? – Svyatoslav Danyliv Apr 13 '22 at 08:32
  • Note that you don't have to switch to lambda syntax for the "Open" constraint - you can either make it part of the `join` or add the `where` clause after the `join`. Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. – NetMage Apr 13 '22 at 18:34
  • 1
    The way `left join` is used hints that `Unit` is the outer table, so there may be Units without packages. By naming it doesn't make sense. I would say that it could be the opposite - `Package` could have multiple units. Considering you are trying to filter all open packages, don't you need to have `Package` be an outer table? I.e., a package may have multiple items, and then it makes sense to count items in a package. – Karlis Fersters Apr 14 '22 at 06:18

0 Answers0