[ACCEPTED]-How can you handle an IN sub-query with LINQ to SQL?-linq-to-sql

Accepted answer

Score: 87

General way to implement IN in LINQ to SQL

var q = from t1 in table1
        let t2s = from t2 in table2
                  where <Conditions for table2>
                  select t2.KeyField
        where t2s.Contains(t1.KeyField)
        select t1;

General way to implement EXISTS in LINQ to SQL

var q = from t1 in table1
        let t2s = from t2 in table2
                  where <Conditions for table2>
                  select t2.KeyField
        where t2s.Any(t1.KeyField)
        select t1;

Score: 65

Have a look at this article. Basically, if you want 4 to get the equivalent of IN, you need to 3 construct an inner query first, and then 2 use the Contains() method. Here's my attempt 1 at translating:

var innerQuery = from fb in FoorBar where fb.BarId = 1000 select fb.FooId;
var result = from f in Foo where innerQuery.Contains(f.FooId) select f;

Score: 3

from f in Foo
    where f.FooID ==
        (
            FROM fb in FooBar
            WHERE fb.BarID == 1000
            select fb.FooID

        )
    select f;

Score: 2

Try using two separate steps:

// create a Dictionary / Set / Collection fids first
var fids = (from fb in FooBar
            where fb.BarID = 1000
            select new { fooID = fb.FooID, barID = fb.BarID })
            .ToDictionary(x => x.fooID, x => x.barID);

from f in Foo
where fids.HasKey(f.FooId)
select f

Score: 1

// create a Dictionary / Set / Collection 1 fids first

Find Other Artilces

var fids = (from fb in FooBar
            where fb.BarID = 1000
            select new { fooID = fb.FooID, barID = fb.BarID })
            .ToDictionary(x => x.fooID, x => x.barID);

from f in Foo
where fids.HasKey(f.FooId)
select f

Score: 0

Try this

var fooids = from fb in foobar where fb.BarId=1000 select fb.fooID
var ff = from f in foo where f.FooID = fooids select f

Score: 0

var foos = Foo.Where<br>
( f => FooBar.Where(fb.BarId == 1000).Select(fb => fb.FooId).Contains(f.FooId));

Score: 0

// create a Dictionary / Set / Collection 1 fids first

Find Other Artilces

var fids = (from fb in FooBar where fb.BarID = 1000 select new { fooID = fb.FooID, barID = fb.BarID }) .ToDictionary(x => x.fooID, x => x.barID);

from f in Foo where fids.HasKey(f.FooId) select f

Score: 0

from f in foo
where f.FooID equals model.FooBar.SingleOrDefault(fBar => fBar.barID = 1000).FooID
select new
{
f.Columns
};

Source: stackoverflow.com

LINQ to SQL query using "NOT IN

convert sql to linq

Query Linq Join Many to Many

How are people unit testing code that uses Linq to SQL

Get table-data from table-name in LINQ DataContext

get last element with linq to sql

How to Connect to SQL Server using LINQ to SQL?

How do I properly use LINQ with MySQL?

Is Injection Possible through Dynamic LINQ?

LINQ to Entities does not recognize the method 'System.TimeSpan Subtract(System.DateTime)' method

Cookie Warning

We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.