Linq Left join, where, group by, count()

Question

I need help with doing a Left join in a linq statement. My T-sql query works as expected but I can't seem to get the wanted results from the Linq. I also realize that there are ton of questions like mine, but I can't seem to apply any of the solutions to my case.

Products table

+---+------------+-----------+
|   |transportID |  Type(int)|
+---+------------+-----------+
| 1 | 5          | 1         |
| 2 | 5          | 3         |
| 3 | 6          | 3         |
+---+------------+-----------+

Stores

+---+------------+-------------+
|   |Name        |Type1(string)|
+---+------------+-------------+
| 1 | Ho         | 1           |
| 2 | He         | 2           |
| 3 | Be         | 3           |
| 4 | Ke         | 4           |
| 5 | Fe         | 5           |
+---+------------+-------------+

My wanted result is

+---+------------+-------------+
|   |Type        |Count        |
+---+------------+-------------+
| 1 | 1          | 1           |
| 2 | 2          | 0           |
| 3 | 3          | 1           |
| 4 | 4          | 0           |
| 5 | 5          | 0           |
+---+------------+-------------+

My tsql that works as intended

SELECT 
    Type1,
    Count(Pro.transportId) as Count

FROM dbo.stores as sto

left Join dbo.products as pro on (sto.Type1 = pro.Type AND pro.transportId=5)

Where Type1 is not null
  group by Type1
  ORDER BY Type1 * 1 ASC

My Linq attempt returns this.

+---+------------+-------------+
|   |Type        |Count        |
+---+------------+-------------+
| 1 | 1          | 1           |
| 3 | 3          | 1           |
+---+------------+-------------+

Linq Statement.

var res =   (from sto in _context.Stores
                             join pro in _context.Products on sto.Type1 equals System.Data.Objects.SqlClient.SqlFunctions.StringConvert((double)pro.Type).Trim()
                             where pro.transportId == transportId
                             group pro by pro.Type1 into pt1
                             select new TypeTransportation()
                             {
                                 Type = pt1.Key, // Needs to be int
                                 Count = pt1.Count()
                             }).ToList();

I've tried doing some defaultifempty but can't seem to make it work.

Answer 1

Basically you need to follow the left join pattern described in join clause (C# Reference). The only tricky part is the pro.transportId=5 condition in

left Join dbo.products as pro on (sto.Type1 = pro.Type AND pro.transportId=5)

The important thing is to not include it as where clause after the join.

One possible way to handle it is like this:

var res = (from sto in _context.Stores
           join pro in _context.Products
           on new { sto.Type1, transportId } equals
              new { Type1 = pro.Type.ToString(), pro.transportId }
           into storeProducts
           from pro in storeProducts.DefaultIfEmpty()
           group sto by sto.Type1 into pt
           select new 
           {
               Type = pt.Key, // the string value, there is no way to convert it to int inside the SQL
               Count = pt.Count()
           }).AsEnumerable() // switch to LINQ to Objects context
           .Select(pt => new TypeTransportation()
           {
               Type = Convert.ToInt32(pt.Type), // do the conversion here
               Count = pt.Count()
           }).ToList();

or just apply it as where clause before the join:

var res = (from sto in _context.Stores
           join pro in _context.Products.Where(p => p.transportId == transportId)
           on sto.Type1 equals pro.Type.ToString()
           into storeProducts
           // the rest ... (same as in the first query)

Another detail to mention is that in order to make LEFT JOIN effectively apply, you need to group by the left table (Stores in your case) field (like in the original SQL query), thus ending up with a string key. If you wish to get the int key, there is no way to do it inside the db query, so you need to use a temporary projection, context switch and the final projection as shown above.

UPDATE: The last thing that I didn't realize initially is that the original SQL Count(Pro.transportId) is excluding NULLs from the right side of the join. So the final correct equivalent LINQ query is:

var res = (from sto in _context.Stores
           join pro in _context.MyProducts
           on new { sto.Type1, transportId } equals
              new { Type1 = pro.Type.ToString(), pro.transportId }
           into storeProducts
           from pro in storeProducts.DefaultIfEmpty()
           group new { sto, pro } by sto.Type1 into pt
           select new
           {
               Type = pt.Key,
               Count = pt.Sum(e => e.pro != null ? 1 : 0)
           })
           .AsEnumerable()
           .Select(pt => new TypeTransportation()
           {
               Type = Convert.ToInt32(pt.Type),
               Count = pt.Count
           }).ToList();

Answer 2

Wow .. lastly i did it ..

      var transportId = 5;
      var res = from s in _context.Stores
                let Type = _context.Stores.Take(1).Select(x => s.Type1).Cast<int>().FirstOrDefault()
                group Type by Type into pt1
                select new TypeTransportation
                {
                    Type = pt1.Key, // Needs to be int
                    Count = _context.Products.Where(i => i.transportId == transportId && i.Type == pt1.Key).Count()
                };            

      foreach (var item in res)
      {
          Console.WriteLine(item.Type + " " + item.Count);
      }

      Console.ReadKey();

Answer 3

I can't do it in query syntax, but using extension method syntax it will be

var products = new[]
{
    new {transportId = 5, Type = 1},
    new {transportId = 5, Type = 3},
    new {transportId = 6, Type = 3},
    new {transportId = 5, Type = 3},
    new {transportId = 5, Type = 5},
};

var stores = new[]
{
    new {Name = "Ho", Type1 = "1"},
    new {Name = "He", Type1 = "2"},
    new {Name = "Be", Type1 = "3"},
    new {Name = "Ke", Type1 = "4"},
    new {Name = "Fe", Type1 = "5"},
};

var transportId = 5;
var res = stores                    
    .GroupJoin(
        inner: products
            .Where(product =>
                product.transportId == transportId),
        innerKeySelector: product => product.Type,
        outerKeySelector: store => Int32.Parse(store.Type1),
        resultSelector: (store, storeProducts) =>
            new
            {
                StoreType = store.Type1,
                StoreName = store.Name,
                ProductsCount = storeProducts.Count()
            })
    .ToList();

foreach (var item in res)
{
    Console.WriteLine(item);
}

Just replace Int32.Parse with appropriate sql function call for actual DbContext query code.

---

With query syntax this is probably the best I can propose:

var res =
    from store in stores
    join product in 
        (from prod in products where prod.transportId == transportId select prod)
        on store.Type1 equals product.Type.ToString() into storeProducts
    select new
    {
        StoreType = store.Type1,
        StoreName = store.Name,
        ProductsCount = storeProducts.Count()
    };

Answer 4

Here is MSDN link "How to: Perform Left Outer Joins" with LINQ: https://msdn.microsoft.com/en-gb/library/bb397895.aspx

You code should be like this:

        var res = (from sto in _context.Stores
               join pro in _context.Products on sto.Type1 equals System.Data.Objects.SqlClient.SqlFunctions.StringConvert((double)pro.Type).Trim() into grpJoin
               from product in grpJoin.DefaultIfEmpty()
               where product.transportId == transportId
               group product by product.Type1 into pt1
               select new TypeTransportation()
               {
                   Type = pt1.Key, // Needs to be int
                   Count = pt1.Count()
               }).ToList();