CODEWITHSUNDEEP
c#asp.netlinqlinq-to-sql
diamond
diamond

Reputation: 69

Selecting the (number of) columns dyanamically using linq to sql

I have a table like this 

    Student   Exam    p1   p2   p3   p4   p5   p6   p7   p8   p9   p10   p11   p12
-----------------------------------------------------------------------------------
    100       unit1   89   56   59   28   48   38   0    0    0     0     0     0
    100       unit2   89   56   59   0     0    0   0    0    0     0     0     0
    100       unit3   89   56   59   28   48   38   0    0    0     0     0     0
    100       unit4   89   56   59   28   48   0    0    0    0     0     0     0

another table 

    Exam   Num_subjects
    ----------------------
    unit1     6
    unit2     3
    unit3     6
    unit4     5

now i need to select the only first 8 columns in the marks table for unit1 as the number of subject for the unit1 is 6 .. how to do this dynamically ... exam is foreign key to the marks table in linq to sql any ideas ...

Upvotes: 0

Views: 777

Answers (3)

Kamal Dua
Kamal Dua

Reputation: 1

Here you don't need to use linq, you can do it with logic

Now just get Num_Subjects like for unit1 = 6

DataTable dt = [whole_table];

int counter = Num_Subjects + 1; //7

string colName = "P" + counter.ToString(); //P7

while(dt.Columns.Contains(colName))
{
   dt.Columns.Remove(colName);
   colName = "P" + (++counter).ToString()
}

At last you we get a table upto P6 columns rest of columns will be deleted.

Upvotes: 0

DarkSquirrel42
DarkSquirrel42

Reputation: 10287

there would be no need for a dynamic query, if those tables were normalized. (check your design)

if you really want to do this dynamically, you'll need an expression tree that handles the select part of your query ... here you can find some more details about a dynamic query lib that can handle that expression tree generation for you (you can provide a string like "new(p1,p2,p3)" and that gets translated to an expression tree)

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1064204

If you have a column based design, since L2S doesn't let you manually materialize (i.e. new MyTable { Foo = row.Foo /* omit some } you are a bit scuppered.

If you just want the data, you could use something like "dapper" which won't have this issue, but you'll need to write the TSQL yourself, i.e.

var rows = conn.Query<MyTable>("select p1, p2, p3, p4, p5 from MyTable where Exam=@exam",
        new { exam }).ToList();

But ultimately, I think I'd prefer a different db schema here...

Upvotes: 1

Related Questions