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.

shouldn't third row in your expected output be `| 3 | 3 | 2` ?? — Utsav, Mar 08 '16 at 09:41
http://stackoverflow.com/questions/3404975/left-outer-join-in-linq — sagi, Mar 08 '16 at 09:43
Utsav, no because im only searching for one specific transportationId at a time. In this case 5. Therefore there is only 1 with Type 3. — user56567675, Mar 08 '16 at 09:43
@sagi, in the answer in that one, I cant seem to put my where clause after the " Join... into ps", it doesnt compile. — user56567675, Mar 08 '16 at 09:45
@user56567675 .. please check my update .. it's working file :) — Moumit, Mar 08 '16 at 12:50

score 3 · Answer 1 · answered Mar 08 '16 at 09:50

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();

Moumit · Answer 2 · 2016-03-08T13:05:08.100

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();

edited Mar 08 '16 at 13:05

answered Mar 08 '16 at 11:19

Moumit

8,314
9
55
59

How?? in what situation? – Moumit Mar 08 '16 at 13:50
@user56567675 Why you delete your comments? Anyway let me know what problem you faced in above query? – Moumit Mar 08 '16 at 14:08

Eugene Podskal · Answer 3 · 2016-03-08T11:15:55.803

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()
    };

I've not been able to find a string to int equivalent that works with EF. — user56567675, Mar 08 '16 at 11:06
@user56567675 So just do it the opposite way, like in the original attempt (int to string). — Eugene Podskal, Mar 08 '16 at 11:08

Ivan Stoev · Answer 4 · 2016-03-08T13:43:38.977

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();

still didnt work, ended up with all the types, which is great, and the correct values for the types that existed. but the ones that were supposed to be 0 were all over the place, most were 1 others were some random value. — user56567675, Mar 08 '16 at 12:39
@user56567675 You are right, I've missed one detail. Try the updated answer (because I don't want to think what SQL is generated with the currently accepted answer). — Ivan Stoev, Mar 08 '16 at 13:51

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

4 Answers4