CODEWITHSUNDEEP
c#linqc#-4.0linq-to-dataset
NIlesh Lanke
NIlesh Lanke

Reputation: 1243

Linq Query for dataset in c#?

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 ";

Upvotes: 0

Views: 1602

Answers (2)

McGarnagle
McGarnagle

Reputation: 102743

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.

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460058

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")
             };

Upvotes: 0

Related Questions