Linq "Where In" subquery to filter data

Question

I want to query a sql server database using Linq to Entities. I want to filter the results using the following Sql Query against the following tables:

DECLARE p0...
SELECT * FROM Boards b
WHERE b.ownerid = p0 or
    b.id IN (SELECT s.boardid FROM Shares s WHERE s.userid = p0)

CREATE TABLE [dbo].[Boards] (
[Id]        INT            IDENTITY (1, 1) NOT NULL,
[OwnerId]   NVARCHAR (128) NULL,
[ShortName] NVARCHAR (50)  NULL,
...);

CREATE TABLE [dbo].[Shares] (
    [BoardId] INT            NOT NULL,
    [UserId]  NVARCHAR (128) NOT NULL
);

A couple caveats, there will be at least one Board row. There can be zero to many Share rows. I want to return the one Board row, and any additional Board rows that have linked Share rows.

I have performed the query using a GroupJoin, SelectMany, and DefaultIfEmpty in effort to perform the equivalent TSQL Outer JOIN but all of those seem way more complex than the TSql equivalent.

How can I perform the equivalent in Linq to filter results such that it uses "Where In" SQL statement and an OR clause with data in the parent table?

See: http://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause — Habib, Feb 24 '15 at 14:05
possible duplicate of [Where IN clause in LINQ](http://stackoverflow.com/questions/959752/where-in-clause-in-linq) — Philip Atz, Mar 03 '15 at 09:04

score 0 · Accepted Answer · answered Feb 24 '15 at 15:10

You could do something like:

var selection = from b in boards
                where b.ownerid == p0 ||
                      (from s in shares
                       where s.userid == p0
                       select s.boardid).Contains(b.id)
                select b;

Alternatively, you could try creating an extension method called WhereIn to do this automatically:

public static class WhereInExtension
{
    public static IEnumerable<T1> WhereIn<T1, T2>(this IEnumerable<T1> table, IEnumerable<T2> container, Func<T1, T2> converter)
    {
        return from b in table
               where container.Contains(converter(b))
               select b;
    }
}

The query (without your b.ownerid == p0 requirement) would then be invoked with:

var selection = boards.WhereIn(from s in shares
                               where s.userid == p0
                               select s.boardid, b => b.id);

Linq "Where In" subquery to filter data

1 Answers1