Error on dt.AsEnumerable() - 'System.Data.DataTable' does not contain a definition for 'AsEnumerable'

Question

This is my clr code in which I am getting the above error. I tried adding reference System.Data.Entity but the problem still remains.

 using System;   
 using System.Data;   
 using System.Data.SqlClient;   
 using System.Data.SqlTypes;    
 using Microsoft.SqlServer.Server;    
 using System.Linq;

 public partial class StoredProcedures
 {

[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProcedure1()
{
    // Put your code here

    DataTable dt = BuildTable();

    DataTable dtTempDates = GetBlankDataTableWithDateColumns(dt);

    for (int col = 1; col < dtTempDates.Columns.Count; col++)
    {
        string columnName = dtTempDates.Columns[col].ColumnName;
        string ymd = columnName.Substring(columnName.IndexOf("|") + 1);


        Double sum = dt.AsEnumerable().Where(r => !String.IsNullOrEmpty(r[columnName].ToString())).Sum(r => (int)r[columnName]);

    }
}

private static DataTable GetBlankDataTableWithDateColumns(DataTable dt)
{
    DataTable datatable = new DataTable();
    datatable.Columns.Add("COUNTRY", typeof(string));

    var distinctDateValues = dt.AsEnumerable()
    .Select(row => DateTime.Parse(row.Field<string>("DATE")))
    .OrderByDescending(x => x)
    .Distinct()
    .ToList();

    int totalDateCount = System.Linq.Enumerable.Count(distinctDateValues);

    for (int row = 0; row < totalDateCount; row++)
    {
        string date = distinctDateValues[row].Date.ToString("M/d/yyyy");

        datatable.Columns.Add("Amount|" + date, typeof(int));
    }

    return datatable;
}

private static DataTable BuildTable()
{
    DataTable datatable = new DataTable("Country");

    datatable.Columns.Add("COUNTRY", typeof(string));
    datatable.Columns.Add("CODE", typeof(string));
    datatable.Columns.Add("DATE", typeof(string));
    datatable.Columns.Add("AMOUNT", typeof(double));


    datatable.Rows.Add(new Object[] { "Ukraine", "0295", "08/16/2013", 122794 });
    datatable.Rows.Add(new Object[] { "USA and Canada", "0614", "08/17/2013", 5671 });
    datatable.Rows.Add(new Object[] { "Algeria", "123", "08/17/2013", 26570 });
    datatable.Rows.Add(new Object[] { "Japan", "789", "08/17/2013", 1883 });
    datatable.Rows.Add(new Object[] { "India", "786", "08/17/2013", 288 });
    datatable.Rows.Add(new Object[] { "India", "786", "08/17/2013", 238 });

    datatable.Rows.Add(new Object[] { "Ukraine", "0295", "08/18/2013", 3568 });
    datatable.Rows.Add(new Object[] { "USA and Canada", "0614", "08/18/2013", 3668 });
    datatable.Rows.Add(new Object[] { "Algeria", "123", "08/18/2013", 785 });

    return datatable;
}

};

I also tried adding System.Data.DataSetExtensions.dll but when I deploy the sp on SQL and run the stored procedure it gives me error: Version is different and assembly does not exist in GAC. I cannot override/alter the assembly as it is being used in other projects.

Can anybody please suggest a workaround to resolve all these issues.

Implement your own version of the method. – Lasse V. Karlsen Jul 04 '14 at 14:43 — Lasse V. Karlsen, Jul 04 '14 at 14:43

score 0 · Answer 1 · answered Jul 12 '14 at 15:52

I'm sure you've come across this older thread in your travels as you mentioned loading the DLL into SQL Server.

LINQ on a DataTable IN a CLR Stored Procedure

As Lasse mentioned, according to one of the first comments in that thread, it is a very simple process to roll your own. I have implemented this in my own project using this method:

public static IEnumerable<DataRow> AsEnumerable(this DataTable dt)
{
    foreach (DataRow r in dt.Rows) yield return r;
}

To use it in a LINQ query, the syntax differs from the built-in AsEnumerable, you can't use d.Field<string>("ColumnName"):

DataTable _tblData;

var tbl = from d in _tblData.AsEnumerable()
          select new { ColumnName = d["ColumnName"] };

Error on dt.AsEnumerable() - 'System.Data.DataTable' does not contain a definition for 'AsEnumerable'

1 Answers1