Using Lambda expressions to query DataTable filled by SQL statement

» chash » Using Lambda expressions to query DataTable filled by SQL statement

By : Sean Dempsey

Date : November 28 2020, 12:01 PM

To fix this issue Assuming the intent of your Linq query is to get the name of customerId 1, try the following...

code :

//This creates the query as IEnumerable<someAnonymousRuntimeObject> but doesn't execute it.
var query = dt.AsEnumerable()
    .Where(i => i.Field<int>("customerId") == 1)
    .Select(i => new { name = i.Field<string>("customerName") });

//This actually executes the query and stores the result in name
var name = query.FirstOrDefault().name;

var name = dt.AsEnumerable()
    .Where(i => i.Field<int>("customerId") == 1)
    .Select(i => i.Field<string>("customerName"))
    .FirstOrDefault();
return name;

var dt2 = dt.AsEnumerable()
    .Where(i => i.Field<int>("customerId") == 1)
    .CopyToDataTable();
return dt2;

public static DataTable getReportById(int customerId)
{
    using (var conn = new MySqlConnection(SqlUpdater.conString))
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT customerName as 'name' FROM customer where customerId = @Id";
        cmd.Parameters.AddWithValue("@Id", customerId);
        MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        return dt;
    }
}

Share :

Convert custom datatable sort to LINQ or lambda expressions

By : rpc4mv

Date : March 29 2020, 07:55 AM

wish helps you You can use DataTableExtensions for the AsEnumerable and CopyToDataTable methods.

code :

public static DataTable SortByGrade(DataTable table,
  string columnToSort, SortDirection sortDirection) 
{

  IEnumerable<DataRow> query = 
    from row in table.AsEnumerable()
    orderby ConvertGrade(row[columnToSort].ToString())
    select row;

  if (sortDirection == "DESC")
  {
    query = query.Reverse();
  }

  DataTable result = query.CopyToDataTable();
  return result;
}

private static int ConvertGrade(string grade)  
{
  string g = grade.ToUpper().Trim();
  int convertedGrade =
    g == "K" ? 0 :
    g == "1" ? 1 :
    g == "2" ? 2 :
    g == "3" ? 3 :
    g == "4" ? 4 :
    g == "5" ? 5 :
    g == "6" ? 6 :
    g == "7" ? 7 :
    g == "8" ? 8 :
    g == "9" ? 9 :
    g == "10" ? 10 :
    g == "11" ? 11 :
    g == "12" ? 12 :
// TODO: Remove these cases when the data is cleaned up
    g == "00" ? 100 :  
    g == "17" ? 100 :
    0;  

  return convertedGrade;  
}

Query expressions vs Lambda expressions

By : Lukasz Konera

Date : March 29 2020, 07:55 AM

should help you out They are the same in the end.
The reason your article's test appears incredibly fast is because of deferred execution. That code isn't actually doing anything at the area they are timing. It will only do something when .ToList() is called.. or another method that forces evaluation of the query (lambda or otherwise). It's quick to interpret the query (incredibly quick, look at the times you've provided), but it's a whole other beast to actually loop over data when the query gets evaluated.

DataTable - Dynamic Linq OrderBy using Lambda expressions

By : user2480845

Date : March 29 2020, 07:55 AM

To fix the issue you can do I'm getting a collection of records in a DataTable and binding it to a grid control. Before binding it I'm sorting the data based on few conditions. For brevity I'm will explain a test scenario. , I assume the array you're talking about is an array of strings.

code :

var columns = new string[] { "Category", "Country" };

var rows = dt.AsEnumerable().OrderBy(x => 0);
foreach(var columnName in columns)
{
    rows = rows.ThenBy(r => string.IsNullOrEmpty(Convert.ToString(r[category])))
               .ThenBy(r => Convert.ToString(r[category]));
}