CODEWITHSUNDEEP
c#.netlinq
Michael
Michael

Reputation: 13616

Creating LINQ with where clause

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?

Upvotes: 0

Views: 139

Answers (2)

Russ Clarke
Russ Clarke

Reputation: 17909

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)
...

Upvotes: 4

Perfect28
Perfect28

Reputation: 11317

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,
                });
    }

Upvotes: 2

Related Questions