Issues With LINQ to SQL Query

Question

I am having trouble getting one of my LINQ to SQL queries to work correctly. I have three tables setup as follows:

Vendors id - primary key

Manufacturers id - primary key

ManufacturerVendorRelationships id - primary key manufacturer_id - foreign key to manufacturer.id vendor_id - foreign key to vendor.id

I am trying to write a LINQ to SQL query that will fetch the Vendors that are not currently related to a given manufacturer. For example, the following table data should only result in 2 vendors for the manufacturer with ID of 1. Can someone please help me with the LINQ to SQL for this example? Right now, the application is doing this logic. Thanks in advance.

Vendors
ID
1
2
3
4
5

Manufacturers
ID
1
2
3
4
5

ManufacturerVendorRelationships
ID               ManufacturerID                   VendorID
1                1                                1
2                1                                2
3                1                                3

What you want is the Linq equivalent of an outer join. http://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join — Rob Rodi, May 07 '12 at 14:18
Sorry, I forgot to post both the Transact-SQL and Linq-to-SQL that I attempted to use. Arion's answer below did the trick. — Grasshopper, May 07 '12 at 14:27

Arion · Accepted Answer · 2012-05-07T14:26:59.400

1

Maybe something like this:

var result=(
        from v in db.Vendors
        where !db.ManufacturerVendorRelationships
                 .Select(s=>s.VendorID)
                 .Contains(v.ID)
        select v
    );

Or if you want it like a field:

var result=(
        from v in db.Vendors
        select  new
        {
            v.ID,
            HasManufacturer=db.ManufacturerVendorRelationships
                           .Select(s=>s.VendorID)
                           .Contains(v.ID)
        }
    );

where db is the linq data context

edited May 07 '12 at 14:26

answered May 07 '12 at 14:18

Arion

31,011
10
70
88

@JustinPihony : Okey. Sorry about that. I will start doing that. – Arion May 07 '12 at 14:34

score 0 · Answer 2 · answered May 07 '12 at 14:29

0

int id= 1;
var result = 
dc.Vendors
  .Where(v => !dc.ManufacturerVendorRelationships
                 .Any(rel => rel.VendorId == v.Id && rel.ManufacturerId == id));

answered May 07 '12 at 14:29

RePierre

9,358
2
20
37

Issues With LINQ to SQL Query

2 Answers2