在 c# 中对数据集进行 Linq 查询

本文关键字：Linq 查询数据集 | 更新日期: 2023-09-27 18:31:25

我有数据集，其中包含两个表，如下所示：

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

如何将以下 SQL 查询写入 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 ";

在 c# 中对数据集进行 Linq 查询

我不确定你为什么按"4,5"排序，但它是这样的：

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();

然后，您可以使用

例如

resultArray[0].FieldID

等。

这可能有效或至少有助于使其正常工作。请注意，我已经更改了某些列的类型。

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