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.
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();
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();
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()
};
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With