Adding Join to Existing LINQ Query

Question

I was kindly helped with this Stack Overflow question.

Now I'm building up the query and need to add joins to other tables to bring in names and descriptions etc.

My query is this:

using (var ctx = new myEntities())
            {
                var pc = ctx.tblPostcodes.Where(z => z.Postcode == postcodeOutward)
                        .Select(x => new {postcodeId = x.PostcodeID}).Single();

                int pcId = pc.postcodeId;

                var q = ctx.tblPrices.OrderByDescending(x => x.Cost)
                .Where(c => c.PostcodeID == pcId)
                .Where(s => s.ItemID < 18)
                .GroupBy(x => x.ItemID)
                .Select(g => new { g, count = g.Count() })
                .ToList()
                .SelectMany(t => t.g.Select(b => b).Zip(Enumerable.Range(1, t.count), (j, i) => new { j.ItemID, j.Cost, j.SupplierID }));

                foreach (var i in q)
                {
                    sb.AppendFormat("ItemId = {0}, Cost = {1}, SupplierId = {2}<hr/>", i.ItemID,  i.Cost, i.SupplierID);
                }
            }

And I'm trying to add the following join:

.Join(ctx.tblItems, it => it.ItemID, s => s.ItemID, (it, s) => new { it, s })

but it causes ambiguous invocation errors. Any ideas? I will need to add two more inner joins. I'm hoping to get one right and the other two will be easy (hopefully).

Depending on you model you should be able to do 'crazy` things (in Stored Proc land) like: `x.Address.PrimaryOwner.Name` and Linq will handle all the required joins in the background. Express the output, not the way you're accustomed to get it. — jessehouwing, Dec 16 '14 at 11:45
new { j.ItemID, j.Cost, j.SupplierID, j.tblItems.Name}. Very cool... — ComfortablyNumb, Dec 16 '14 at 12:05

Florian Schmidinger · Accepted Answer · 2014-12-16T11:53:23.370

3

If you use database first EF generates navigational properties for you so you dont have to join.

If you want to be able to get this information outside the query, use the .Include("Navigational Propertyname") command to add your 'Joins' which will result in adding the respective objects or list of objects to the query result.

 var q = ctx.tblPrices.Include("tblItems").OrderByDescending(x => x.Cost)

Better look at the EF Model to find out what the property is called...

edited Dec 16 '14 at 11:53

answered Dec 16 '14 at 11:47

Florian Schmidinger

4,682
2
16
28

This was a penny dropping, eureka moment for me. Thank you and @jessehourwing! – ComfortablyNumb Dec 17 '14 at 08:42

Kutyel · Answer 2 · 2014-12-16T12:02:43.483

Try this, I don't know if it will work in the first try, but it is a good starting point!

using (var ctx = new myEntities())
{
    var pc = ctx.tblPostcodes
        .First(x => x.Postcode == postcodeOutward)
        .Select(x => new { postcodeId = x.PostcodeID });

    var prices = ctx.tblPrices
        .Where(x => x.PostcodeID == pc.postcodeId)
        .OrderByDescending(x => x.Cost)
        .ToList();

    var items = ctx.tblItems
        .Where(y => y.ItemID < 18)
        .GroupBy(y => y.ItemID)
        .Select(y => new { y, count = y.Count() })
        .ToList();

    // Join
    var q = prices
        .Join(items,
            pr => pr.ItemID,
            it => it.ItemID,
            (pr, it) => new
            {
                pr.ItemID,
                pr.Cost,
                pr.SupplierID
            })
        .ToList();

    q.Select(x => sb.AppendFormat("ItemId = {0}, Cost = {1}, SupplierId = {2}<hr/>", 
        x.ItemID, x.Cost, x.SupplierID));
}

Adding Join to Existing LINQ Query

2 Answers2