Creating LINQ with where clause

Question

I have this LINQ:

    private static object getObjectModels(DbContext context, IQueryable<int> contractsID)
    {
        return (from objectModel in context.Set<ObjectModel>()
                where "column named conId contains contractsID "
                select new ContractHelper
                {
                    Id = contract.Id,
                    ClientId = contract.ClientId,
                });
    }

I need to select from table records where column named conID have values of contractsID.

The contractsID is int array. The conID is int value column.

What have I write in this row: where "column named conId contains contractsID" to get all records where column conID have item that equal to item in contractsID array?

You need to replicate an `IN` clause: [http://stackoverflow.com/questions/959752/where-in-clause-in-linq](http://stackoverflow.com/questions/959752/where-in-clause-in-linq) — markpsmith, Oct 06 '15 at 13:01

Russ Clarke · Accepted Answer · 2015-10-06T13:25:02.110

4

You might be able to invert the where clause and use a 'contains', such as:

private static object getObjectModels(DbContext context, IQueryable<int> contractsID)
    {
        return (from objectModel in context.Set<ObjectModel>()
                where objectModel.conId.HasValue && contractsID.Contains(objectModel.conId)
                select new ContractHelper
                {
                    Id = contract.Id,
                    ClientId = contract.ClientId,
                });
    }

You might need to convert the IQueryable to a list however.

var myIds = contractIDs.ToList();
...
where myIds.Contains(objectModel.conId)
...

edited Oct 06 '15 at 13:25

answered Oct 06 '15 at 13:04

Russ Clarke

17,511
4
41
45

objectModel.conId column is nullable so I get error when Itry to use your example.Is there any wy to fix it? – Michael Oct 06 '15 at 13:16
I'll update my answer with a suggestion, I don't have an EF project to hand to test it though! – Russ Clarke Oct 06 '15 at 13:21

score 2 · Answer 2 · answered Oct 06 '15 at 13:06

You can go with an int array to make linq translate to the correct IN SQL syntax

  private static object getObjectModels(DbContext context, IQueryable<int> contractsID)
    {
         // Necessary to translate Contains to SQL IN  CLAUSE
        int [] contractIdsArray = contractsID.ToArray() ; 

        return (from objectModel in context.Set<ObjectModel>()
                where contractIdsArray.Contains(objectModel.conId)
                select new ContractHelper
                {
                    Id = contract.Id,
                    ClientId = contract.ClientId,
                });
    }

Creating LINQ with where clause

2 Answers2