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
– jessehouwing, Commented Dec 16, 2014 at 11:45
new { j.ItemID, j.Cost, j.SupplierID, j.tblItems.Name}. Very cool... — ComfortablyNumb
– ComfortablyNumb, Commented Dec 16, 2014 at 12:05

Florian Schmidinger · Accepted Answer · 2014-12-16 11:53:23Z

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, 2014 at 11:53

answered Dec 16, 2014 at 11:47

Florian Schmidinger

4,6822 gold badges19 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ComfortablyNumb Over a year ago

This was a penny dropping, eureka moment for me. Thank you and @jessehourwing!

Kutyel · Accepted Answer · 2014-12-16 12:02:43Z

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

Collectives™ on Stack Overflow

Adding Join to Existing LINQ Query

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related