LINQ query with a where condition containing

Question

I am just learning LINQ and I have come across and issue Im not sure how to do in LINQ.

string numbers = "1,3,4,5";
string[] outletsInaStringArray = outlets.Split(',');
List<string> numbersAsAList = outletsInaStringArray.ToList();

I have a field in my database which holds a number. I only want to select the lines WHERE the number in the database is IN the line list of numbers "1,3,4,5" (these numbers are just examples).

Thanks in advance

---

I have looked at Tim and James answers and also looked at the line that James has sent. Im still a bit confused.....Sorry. Below is my actual code. It compiles but does not work

string outlets = "1,3,4,5"
string[] outletsNeeded = outlets.Split(',');
List<string> outletsNeededList = outletsNeeded.ToList();

DashboardEntities1 db = new DashboardEntities1();
var deptSalesQuery =  (
from d in db.DashboardFigures
where (d.TypeOfinformation == "DEPTSALES") && (outletsNeeded.ToString().Contains(d.OutletNo.ToString())) 
                                     select new DeptSales
                                     {
                                          Dn = (int)d.Number,
                                          Dnm = "Mens",
                                          On  = d.OutletNo,
                                          Qs = (double)d.Value_4,
                                          Se = (double)d.Value_2,
                                          Si = (double)d.Value_3
                                     }                                    
                                 );

In the DASHBAORDFIGURES table in SQL I have 2 records where the outlets number = 1, and therefore should have come up with two records. Sorry if this is a simple thing, its just new to me and its frustrating.

Answer 1

You can use Contains as tagged:

var query = db.Table
    .Where(x => outletsInaStringArray.Contains(x.Number) && x.information == "SALES");

that was method syntax, if you prefer query syntax:

var query = from figure in db.Figures
            where outletsInaStringArray.Contains(figure.number)
              &&  figure.information == "SALES"
            select figure;

---

But the column number is int, the List<string> stores strings, maybe your LINQ provider does not support .Contains(figure.ToString()). Then convert the strings to int first:

List<int> outletsNeededList = outletsNeeded.Select(int.Parse).ToList();

Answer 2

The answer that Tim provided is one method. Linq and lambda are interchangeable. Have a look at the following posting as well. Link

var result = from x in db.Table.ToList()
             where outletsInaStringArray.Contains(x.Number)
             select x;

Also have a look the following as it offers a very similar solution to the one you are looking for: Link

Answer 3

As per i understand, you want to fetch data in similar way as IN (SQL) clause does it.

SELECT <Field_List>
FROM Table
WHERE IntegerField IN (1,2,4,5)

But i'm wondering why do you want to do it that way, when you can join data and get only matches. The worse is that you're trying to mix different data type and pass comma delimited text as a set of integers (i may be wrong):

SELECT <Field_List>
FROM Table
WHERE IntegerField IN ("1,2,4,5")

Above query won't execute, because the set of integers is "packed" into comma delimited string. To be able to execute that query, a conversion between data types must be done. Numbers in a string have to be converted to a set of integers (using user define split function or Common Table Expression):

;WITH CTE AS
(
     --here convertion occurs
)
SELECT t2.<Field_List>
FROM CTE As t1 INNER JOIN TableName AS t2 ON t1.MyNumber = t2.IntegerField 

Linq + any programming language is more flexible. You can build a list of integers (List) to build query.

See simple example:

void Main()
{

    List<MyData> data = new List<MyData>{
                    new MyData(1,10),
                    new MyData(2, 11),
                    new MyData(5, 12),
                    new MyData(8, 13),
                    new MyData(12, 14)
                    };

    //you're using comma delimited string 
    //string searchedNumbers = "1,3,4,5";
    //var qry = from n in data 
    //      join s in searchedNumbers.Split(',').Select(x=>int.Parse(x)) on n.ID equals s 
    //      select n;
    //qry.Dump();

    List<int> searchedNumbers = new List<int>{1,2,4,5};
    var qry = from n in data 
            join s in searchedNumbers on n.ID equals s 
            select n;
    qry.Dump();

}

// Define other methods and classes here
class MyData
{
    private int id = 0;
    private int weight = 0;

    public MyData(int _id, int _weight)
    {
        id = _id;
        weight = _weight;
    }

    public int ID
    {
        get{return id;}
        set {id = value;}
    }

    public int Weight
    {
        get{return weight;}
        set {weight = value;}
    }
}

Result:

ID  Weight
1   10 
5   12 

Cheers
Maciej

Answer 4

Thank you all iv now got it to work using all your suggestions

the final code that works is as follows

DeptSales myDeptSales = new DeptSales();                  // Single department
List<DeptSales> myDeptSalesList = new List<DeptSales>();  // List of Departments
DashboardEntities1 db = new DashboardEntities1();

var deptSalesQuery = from d in db.DashboardFigures
join s in outlets.Split(',').Select(x => int.Parse(x)) on d.OutletNo equals s
where (d.TypeOfinformation == "DEPTSALES")
select new DeptSales
                   {
                    Dn = (int)d.Number,
                    Dnm = "Mens",
                    On = d.OutletNo,
                    Qs = (double)d.Value_4,
                    Se = (double)d.Value_2,
                    Si = (double)d.Value_3
                    };

Thanks once again.