How to do Many To Many LINQ entity grouping queries?

Question

I can see many examples on this site of the sort of queries I'm after, but I can't relate to them and how they work. I was wondering if you could help me.

I have set up my many-to-many tables and relationships with entity designer in Visual Studio.

tblQuotes
ID | QuoteNo | Date

tblItems
ID | PartNo | Desc

tblSuppliers
ID | Supplier | email

tblQIS (quotes items suppliers)
ID | SupplierID | QuoteID | ItemID

I've put some test data in and have begun to try to type this, but I think I first need to group by quoteNo then group by supplier, to get the details in the correct view.

var tblQuotes = from d in db.tblQuotes_Items_Suppliers
                    .Include(t => t.tblItems)
                    .Include(t => t.tblQuotes)
                    .Include(t => t.tblSuppliers)
                group by (d.QuoteID,d.SupplierID)
                select d;

Can anyone help me out?

i3arnon · Accepted Answer · 2014-01-03 14:29:16Z

1

+50

you could try this:

var tblQuotes = 
    from d in db.tblQuotes_Items_Suppliers    // or tblQIS whatever the name
    group d by new
    {
        d.QuoteID,
        d.SupplierID
    }  into g
    select g;

that would give you Quotes grouped by both QuoteID and SupplierID

Update

the tblQuotes is a list (IQueryable) of grouped quotes, so you can access other entities as follow:

 var firstGroupOfQuotes = tblQuotes.First(); // will give you the first group of quotes
 var firstQuote = firstGroupOfQuotes.First(); // will give you the first quote in the first group
 var item = firstQuote.tblItems; // will give you the item of this quote
 var partNo = item.PartNo; // will give you the PartNo of this item

edited Jan 3, 2014 at 14:29

i3arnon

117k33 gold badges334 silver badges358 bronze badges

answered Dec 12, 2013 at 11:35

Ahmad Ibrahim

1,9352 gold badges15 silver badges33 bronze badges

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

7 Comments

AlexW Over a year ago

how would i get my other tables into this, i need data from those too

AlexW Over a year ago

in the group statement im getting "the inference failed in the call to 'GroupBy'" group is underlined

Ahmad Ibrahim Over a year ago

you mean "Type inference failed..." ?

AlexW Over a year ago

yeah type inference sorry, how do i fix that?

Ahmad Ibrahim Over a year ago

are you sure that ID fields have same dataType ?

|

Collectives™ on Stack Overflow

How to do Many To Many LINQ entity grouping queries?

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related