如何将LINQ查询结果直接转为DataTable？类型转换报错求助

Hey there, let's break down why you're hitting that error and how to fix it properly.

First, the root of the error: Your LINQ query returns an IEnumerable<SummuryReportTraffic> (or an anonymous type if you modified it), but you're trying to convert that directly to IEnumerable<DataRow>. Those types don't have an implicit conversion—DataRow objects are tightly tied to a specific DataTable structure, so you can't just cast a custom class to one.

Here's the step-by-step solution to convert your LINQ results to a DataTable with cleaner code (we still handle dynamic columns, but it's more streamlined than your original nested loops):

Step 1: Build the DataTable structure first #

Since your descriptions property has dynamic column names (the title values), we need to collect all unique comment titles first, then add those plus your fixed columns (siteName, exchangeCounter) to the DataTable.

// Grab all unique comment titles across all reports
var allUniqueComments = smallExchangeReport
    .SelectMany(report => report.descriptions)
    .Select(desc => desc.title)
    .Distinct()
    .ToList();

// Initialize the DataTable with fixed columns
var smrTable = new DataTable();
smrTable.Columns.Add("siteName", typeof(string));
smrTable.Columns.Add("exchangeCounter", typeof(int));

// Add dynamic columns for each unique comment
foreach (var commentTitle in allUniqueComments)
{
    smrTable.Columns.Add(commentTitle, typeof(int));
}

Step 2: Populate the DataTable with LINQ results #

We can use LINQ's ToList().ForEach() to make this cleaner than nested foreach loops, while still handling the dynamic column assignments:

smallExchangeReport.ToList().ForEach(item =>
{
    DataRow newRow = smrTable.NewRow();
    // Set fixed column values
    newRow["siteName"] = item.siteName;
    newRow["exchangeCounter"] = item.exchangeCounter;
    // Set values for dynamic comment columns
    item.descriptions.ToList().ForEach(desc =>
    {
        newRow[desc.title] = desc.numbers;
    });
    smrTable.Rows.Add(newRow);
});

Alternative: Use LoadDataRow for strict column ordering #

If you prefer to align row values with column order explicitly (great for avoiding key mismatches), you can use LoadDataRow instead:

foreach (var item in smallExchangeReport)
{
    var rowValues = new List<object>
    {
        item.siteName,
        item.exchangeCounter
    };
    // Add values for each dynamic column (fallback to 0 if no matching comment)
    foreach (var commentTitle in allUniqueComments)
    {
        var matchingDesc = item.descriptions.FirstOrDefault(d => d.title == commentTitle);
        rowValues.Add(matchingDesc?.numbers ?? 0);
    }
    smrTable.LoadDataRow(rowValues.ToArray(), LoadOption.Upsert);
}

The key takeaway here is that DataRow can't be directly cast from your custom LINQ results—you need to first define the table structure that matches your data, then map each property value to the corresponding column.

内容的提问来源于stack exchange，提问作者Michael