Linq Query for dataset in c#?

Question

I have dataset which contains two tables like this:

DataTable dtFields = new DataTable("tmpFieldTable");

dtFields.Columns.Add("FieldID");
dtFields.Columns.Add("CDGroupID");
dtFields.Columns.Add("CDCaption");
dtFields.Columns.Add("fldIndex");

DataTable dtCDGroup = new DataTable("tmpCDGroup");

dtCDGroup.Columns.Add("CDGroupID");
dtCDGroup.Columns.Add("Name");
dtCDGroup.Columns.Add("Priority");

DataSet ds = new DataSet("tmpFieldSet");

ds.Tables.Add(dtFields);
ds.Tables.Add(dtCDGroup);

How can I write following SQL query to LINQ

queryString = "Select FieldID, tmpCDGroup.Name, CDCaption, IIF(ISNULL(Priority),99,Priority), fldIndex from tmpFieldList LEFT OUTER JOIN tmpCDGroup ON tmpFieldList.CDGroupID = tmpCDGroup.CDGroupID order by 4,5 ";

McGarnagle · Answer 1 · 2012-04-17T15:36:06.687

1

I'm not sure why you're ordering by "4,5", but it would be like this:

var resultArray = tmpFieldList.Join(
    tmpCDGroup,                           // inner join collection
    fieldList => fieldList.CDGroupID,     // outer key selector
    cd => cd.CDGroupID,                   // inner key selector
    (fieldList, cd) => new {             // result selector
        FieldID = fieldList.FieldID, 
        Name = cd.Name, 
        CDCaption = cd.CDCaption, 
        Priority = fieldList.Priority ?? 99, 
        fldIndex = fieldList.fldIndex
     })
.OrderBy(result => result.Priority)
.ThenBy(result => result.fldIndex)
.ToArray();

Then you can access using, for example,

resultArray[0].FieldID

, etc.

edited Apr 17 '12 at 15:36

answered Apr 17 '12 at 07:32

McGarnagle

101,349
31
229
260

As i am new LINQ, please tell me how to extract result from above quey? – NIlesh Lanke Apr 17 '12 at 07:37
@NIleshLanke I updated the answer, hope that helps. It's just an array of the anonymous type with "FieldID", "Name", etc. properties. – McGarnagle Apr 17 '12 at 07:43
@NellshLanke: Have a look at [Linq 101](http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b). There are many great examples to get started with LINQ. – raznagul Apr 17 '12 at 07:47
2

@dbaseman: `order by 4, 5` in SQL means ordering by the 4th and 5th column. So the order by should look like `.OrderBy(result=>result.Priority).ThenBy(result=>result.fldIndex)`. – raznagul Apr 17 '12 at 07:49
@raznagul ah, thanks, I had never seen that syntax before. Answer is corrected. – McGarnagle Apr 17 '12 at 07:51
@dbaseman Above code throws an Exception as specified cast is not valid – NIlesh Lanke Apr 17 '12 at 09:18
@raznagul, I wonder if that's because of the part the sets Priority to default. Can you try the above update? – McGarnagle Apr 17 '12 at 15:48
If the values of Priority have different types the `OrderBy` trows an `ArgumentException`. I haven't encounter other Exceptions. – raznagul Apr 18 '12 at 12:10

Tim Schmelter · Answer 2 · 2012-04-17T08:12:26.373

This might work or at least help to get it working. Note that i've changed the type of some columns.

var result = from field in dtFields.AsEnumerable()
             join cdGroup in dtCDGroup.AsEnumerable()
             on field.Field<int>("CDGroupID") equals cdGroup.Field<int>("CDGroupID") 
                    into fieldGroup
             from row in fieldGroup.DefaultIfEmpty()
             let priority = row.IsNull("Priority") ? 99 : row.Field<int>("Priority")
             orderby priority, row.Field<int>("fldIndex")
             select new
             {
                 FieldID   = row.Field<int>("FieldID"),
                 GroupName = row.Field<int>("Name"),
                 CDCaption = row.Field<int>("CDCaption"),
                 Priority  = priority,
                 fldIndex  = row.Field<int>("fldIndex")
             };

Linq Query for dataset in c#?

2 Answers2