CODEWITHSUNDEEP
c#winformslinq-to-sql
Teo Laferla
Teo Laferla

Reputation: 41

Retrieving data from sql to datagridview using LinqToSql

I have a datatable in sql and a datagridview in winform. datatable holds measurement results from a mould with a MouldID. For every measurement 50 lines of results are logged to table. To track measurement count for same mould, i also have MeasId column which incremented by 1 for every measurement input. Please see picture for table view. Table Screenshot What i need to do, retrieve only the rows with choosen MouldID (from a combobox) with last MeasID. I tried following codes but i couldn't figure out how to group this rows with MeasId.

using (LinqDataClassesDataContext dataContext = new 
LinqDataClassesDataContext())
{
    // attemp 1
    var query=dataContext.SupplierVals                   
            .Where(m=>m.MouldID==comboBMouldID.SelectedValue.ToString())
            .OrderByDescending(m => m.MeasId).FirstOrDefault();

    // attemp 2
    var query=dataContext.SupplierVals                      
            .Where(mr=>mr.MouldID==comboBMouldID.SelectedValue.ToString())
            .OrderByDescending(mr => mr.MeasId).Select();

    // attemp 3
    var query = (from x in dataContext.SupplierVals
               where x.MouldID == comboBMouldID.SelectedValue.ToString()
               select x).First(); 

    // attemp 4
    var query = from x in dataContext.SupplierVals
            where x.MouldID == comboBMouldID.SelectedValue.ToString()
            group x by x.MeasId into grp
            select grp.OrderByDescending(x => x.MeasId).First();

daGridUnused.AutoGenerateColumns = false;
daGridUnused.Columns["unusedShowDist"].DataPropertyName = "Distnc";
daGridUnused.Columns["unusedShowAper"].DataPropertyName = "Apert";
daGridUnused.Columns["unusedShowTap"].DataPropertyName = "Taper";

daGridUnused.DataSource = query;

}

None of these queries return what i need from datatable. What am i doing wrong?

Upvotes: 1

Views: 35

Answers (1)

Mong Zhu
Mong Zhu

Reputation: 23732

It seems that you were almost there. You simply need to filter also by the Max value and order by the ValueId:

string mouldId = comboBMouldID.SelectedValue.ToString();
int max = dataContext.SupplierVals                   
        .Where(m=>m.MouldID == mouldId)
        .Max(m => m.MeasId);
var query=dataContext.SupplierVals                   
        .Where(m=>m.MouldID == mouldId && m.MeasId == max).ToList();

disclaimer: this query can surely be optimized, I am working on a better solution

Upvotes: 1

Related Questions