How to Use Multiple 'IN' on a DataTable

Question

I have asked how to use the 'IN' here.

So far, this SQL Server query:

select * 
  from table 
  where column1 in 
    (
        select column2 
        from table
    )

can be translated as:

table.Select(
    string.Format("column1 in ({0})",
    string.Join(",", table Rows.OfType<DataRow>()
        .Select(r=>r["column2"].ToString())
        .Distinct())));

My question is how to translate this SQL query:

select column3
  from table
  where column1 in 
    (
        select column2
        from table
    )

So that I can use what's inside on column3 on another 'IN'.

(e.g.)

select columnA
  from table2 
  where columnB in 
    (
        select column3 
        from table1 
        where column1 in 
            (
                select column2 
                from table1
            )
    )

The big problem I see here is performance, nested INs, are to me, crying out to be replaced by joins. I don't know enough LINQ to model that though. — Russ Clarke, Aug 22 '11 at 09:15
I need to convert the SQL command in C#, like how the first SQL command is converted. — Gerald Torres, Aug 22 '11 at 09:16
@Russ, Can you please give me an example SQL query that is the same as the first and third SQL statement above that uses Join? — Gerald Torres, Aug 22 '11 at 09:18
Added as an answer, formatting doesn't work in comments for this stuff. — Russ Clarke, Aug 22 '11 at 09:28
Just to be clear, do you actually mean in that first statement that you want to select all rows from table where column1 = column2 ? — Russ Clarke, Aug 22 '11 at 09:30
Thanks, but I still need to change those SQL statements to C# DataTable... — Gerald Torres, Aug 22 '11 at 09:34
@Russ, What I did in in the first SQL statement is to return all rows that the column1 values is included in column2. Doesn't seem clear enough... If Column1 has 1,2,3, and column2 has 1,3,1, respectively, row 1 and 3 will be returned. — Gerald Torres, Aug 22 '11 at 09:44
Removed my answer, sorry - wasn't aware of the data before I wrote it up. — Russ Clarke, Aug 22 '11 at 10:12

score 0 · Answer 1 · answered Aug 23 '11 at 13:52

0

Linq is a tool. It has its strengths and its limitations.

What I would do is use a custom query to accomplish this. Let SQL Server do the brunt work here, not Linq. It'll dramatically improve the performace, as well.

var sqlTxt = "select columnA "  +
    "from table2 "  +
    "where columnB in "  +
    "( select column3 "  +
    " from table1 "  +
    " where column1 in " +
        "(select column2 " + 
            "from table1) " +
    ")"

results = ExecuteQuery(sqlTxt);

answered Aug 23 '11 at 13:52

Richard

6,215
4
33
48

why the horrible string building instead or a string literal? – Manatherin Aug 23 '11 at 14:39
(1) The compiler [simplifies this to a string literal](http://www.yoda.arachsys.com/csharp/stringbuilder.html), applied directly to the variable. (2) doing this allows you to concatenate to it, format it, etc. (3) This isn't meant to be optimal C# code. Just an example of how to fix Linq. – Richard Aug 23 '11 at 14:48
@Richard, In other words, I should directly do this in the database. I can't since the data in the DataTable is **not** in the Database. This is what should happen: put all data from a database table to a DataTable() (done that), the user change the items in the DataTable, and when saving the data from the DataTable to another table in the database, the DataTable must be validated first before doing so. The SQL commands above are only an example for what I need to do with my DataTable. – Gerald Torres Aug 24 '11 at 01:41

How to Use Multiple 'IN' on a DataTable

1 Answers1