Retrieving data from sql to datagridview using LinqToSql

Question

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. 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?

Whole project is built-up with linq, so i am kinda stuck with it now — Teo Laferla
– Teo Laferla, Commented Jun 18, 2019 at 8:10
" with last MeasID. " does that mean the most recent entry from all of the entries with the highest MeasID ? — Mong Zhu
– Mong Zhu, Commented Jun 18, 2019 at 8:12

Mong Zhu · Accepted Answer · 2019-06-18 08:50:12Z

1

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

edited Jun 18, 2019 at 8:50

answered Jun 18, 2019 at 8:16

Mong Zhu

23.8k10 gold badges47 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Teo Laferla Over a year ago

FYI, it didn't return any data from table also.

Teo Laferla Over a year ago

This is the challenge actually. Now on the table, there is only 1 MouldID and 3 MeasId for this mould. But in real app, there will be lots of MouldIDs and measurements for them. Data needs to be distinguished by these 2 paramater. I need rows displays (let2

Mong Zhu Over a year ago

@TeoLaferla this is not the point, the point is that you have multiple rows having the same MouldId, the same MeasId and even the same Date! From your examply it seems that you can distinguish them only by the column ValueID. How far away from the reality is your posted example? I cannot read minds, I can only provide a solution for the posted problem according to your description

Mong Zhu Over a year ago

@TeoLaferla ok then simply remove the orderby clause. I got distracted , because you used in 3 of 4 of your posted queries either First or FirstOrDefaul. I thought you need only 1 row. I edited my post. Have a look

Teo Laferla Over a year ago

Thanks a lot. That was it.

|

Collectives™ on Stack Overflow

Retrieving data from sql to datagridview using LinqToSql

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related