Sql query with multiple ids in .NET

Question

I have following method

public SomeObj LoadSomeData(int id)
{
    using (var context = new DataContext())
    {
        var result = 
            context.Database.SqlQuery<SomeObj>
            ($"SELECT * FROM SOMEOBJECT WHERE id = {id}")
        return result;
    }
}

This works fine, now the specification has changed, and I need to lookup multiple ids. I get ids as list of int and return a list. I read a bit about Sql Query and I can use IN and pass multiple values. So this is my changes:

public List<SomeObj> LoadSomeData(List<int> listOfIds)
{
    using (var context = new DataContext())
    {
        var result = 
            context.Database.SqlQuery<SomeObj>
            ($"SELECT * FROM SOMEOBJECT WHERE id IN ({id})") <-- I am not sure how to pass list in 
        return result;
    }
}

The second method works when I pass values manually to the query like 1,2,3 etc. but how can I pass list of to query?

What do you mean multiple id's, instead of returning a specific id you want to grab a batch of records with those id's? — Greg, Mar 28 '18 at 21:57
https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code — Joe Farrell, Mar 28 '18 at 22:00

Maytham Fahmi · Accepted Answer · 2019-03-14T09:48:45.283

6

This can be achieved different ways. I would suggest one way to go is using Join method.

var ids = string.Join(",", listOfIds)

This would return a list of {1, 2, 3} to string of 1, 2, 3

So putting that together with your final method would look like this:

public List<SomeObj> LoadSomeData(List<int> listOfIds)
{
    var ids = string.Join(",", listOfIds)
    using (var context = new KmdDataContext())
    {
        var result = 
            context.Database.SqlQuery<SomeObj>
            ($"SELECT * FROM SOMEOBJECT WHERE id IN ({Ids})")
        return result;
    }
}

edited Mar 14 '19 at 09:48

answered Mar 28 '18 at 21:58

Maytham Fahmi

31,138
14
118
137

1

thank you this sovled the issue, i can not vote, but will accept this – Maya Rager Mar 28 '18 at 22:02

stuartd · Answer 2 · 2018-03-28T22:04:50.083

4

You can pass them by generating the list:

var idList = string.Join(",", listOfIds);
$"SELECT * FROM SOMEOBJECT WHERE id IN ({idList})")

But if the list gets big (from memory, more than 2000 or so) that will fail. If you need more than that, use a Table-Valued Parameter - a bit more effort to set up, but they are very neat and scalable.

edited Mar 28 '18 at 22:04

answered Mar 28 '18 at 21:59

stuartd

70,509
14
132
163

Ahmad.Tr · Answer 3 · 2018-03-28T22:34:34.583

3

Try String.Join(",", listofids) to convert the list into a comma separated string that you can use in the In part of the sql query

edited Mar 28 '18 at 22:34

answered Mar 28 '18 at 22:00

Ahmad.Tr

756
7
22

I guess i was a bit slow , the guys answered the same as i did . +1 for them – Ahmad.Tr Mar 28 '18 at 22:02
1

Since .Net 4 you don't have to create an array with `ToArray()` as you can pass an `IEnumerable` – stuartd Mar 28 '18 at 22:30
1

Ahmad listofids already a list why would you convert it to array? and join is with capital Join. but else you get my vote as well. – Maytham Fahmi Mar 28 '18 at 22:32

score 2 · Answer 4 · answered Mar 28 '18 at 22:06

2

If you already have a collection, you could simply do.

var ids = String.Join(",", parameters.Where(value => value > 5 && value < 10));

Now you would be able to pass the variable to query.

var query = $"SELECT * FROM ... WHERE Id IN ({ids})";

answered Mar 28 '18 at 22:06

Greg

11,302
2
48
79

1

.. where do the values 5 and 10 come from? – stuartd Mar 28 '18 at 22:14
1

@stuartd Just used it as a basic where clause to filter the collection down to place the range. Assuming the list has more than he would like to actually query against. – Greg Mar 28 '18 at 22:20

score 1 · Answer 5 · answered Mar 28 '18 at 22:57

Here is an approach using LINQ and Entity Framework. From your example, there is no apparent need to use raw SQL.

public List<SomeObj> LoadSomeData(List<int> listOfIds)
{
    using (var context = new DataContext())
    {
        return context.SomeObj.Where(x => listOfIds.Contains(x.id)).ToList();
    }
}

This is the equivalent to WHERE id in (1,2,3)

Sql query with multiple ids in .NET

5 Answers5