More adventure: Grouping and Extracting max() values for a base query

Question

Plz assume i have a datatable such that:
ID int unique
Contract varchar

Suppose I want something like:
Select ID, Contract from MYTABLE where ID > @LastID Group by ID, Contract

I then need to extract the max(ID) and also the distinct lists of Contracts

I suppose I need a few Queries to make this happen:

var query = from h in db.MYTABLE where h.ID > maxId select new { ID = h.ID, Contract = h.Contract };
var maxID = query.Max(h=> h.MessageID);
var contracts = from h in query group h by h.Contract into g select new {Contracts = g};

Does this seem right and, am I doing it efficently?  Is there a better way?
Because, it seems really messy to me! :)

Answer 1

Hi Bill,

 

Glad to see you again!

 

Based on the three queries in your code snippet, I think you are trying to retrieve the MAX ID among the data table WHERE ID is larger than the maxid variable.  Then you try to get the distinct Contracts via GROUP BY.  Based on my understanding, records in the data table can have same Contract names but different ID, correct?   If that’s the case, I think the three queries in your code snippet are fine.  Let’s analysis this problem in detail: J

 

As I have mentioned in this related thread, due to LINQ to SQL’s lazy loading feature, the T-SQL to retrieve the data is executed only when we try to retrieve the value of the object.   So after the first query, data is not actually returned from the database.  Then after the second max query, the certain T-SQL is executed and the max value of ID is returned.  The T-SQL here is generated based on the first and second queries together.  After the third group by query, the data is not returned since we have not tried to access the data. 

 

Besides, does the data table also contains some other columns besides the ID and Contract columns?   If so, your queries using anonymous types is great to filter the data.  Otherwise, if the table only contains the ID and Contract columns, you can refer to the following queries to make it simpler:

========================================================================================
                var maxID = db.MYTABLE.Where(c => c.ID > maxid).Max(c => c.ID);

 

                var contracts = from c in db.MYTABLE

                                group c by c.Contract into g

                                select new { Contracts = g };
========================================================================================

 

The contracts variable holds a collection of IGrouping objects.  And each IGrouping object has a Key property holding the certain distinct Contract as the key.

 

If you have any questions, please feel free to let me know.

 

Have a great day!

 

 

Best Regards,

Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Thanks Lingzhi again for your helping out!

Yes the original SQL table definition has a number of fields and I've stripped them down to just a handful per my DBML file but still, just need a subset of the two mentioned for this particular query work.

Deeper thinking here suggets that I should put the 1st query into a datatable since really, when I do my processing, I need to be working with the same data on subsequent manipulations.

So, this should still start the ball rolling:
var query = from h in db.MYTABLE where h.ID > maxId select new { ID = h.ID, Contract = h.Contract };

How do I go about loading the above into a DataTable?

Answer 3

Hi Bill,

 

If we have such a DataTable with two columns “ID” and “Contract”:

================================================================= 
DataTable dt = new DataTable();

dt.Columns.Add("ID", typeof(int));

dt.Columns.Add("Contract", typeof(string));
================================================================= 

 

Then we can use a simple foreach loop to put the query into this DataTable:

================================================================= 
foreach (var i in query)

{

    dt.Rows.Add(i.ID, i.Contract);

}
================================================================= 

 

Besides, do you want to perform some databinding, so you load the data into a DataTable?   If so, LINQ to SQL data can directly be bound.  For detail, please see http://msdn.microsoft.com/en-us/library/bb546190.aspx.   Also, with a DataTable, we can still use LINQ via LINQ to DataSets. 

 

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail] 

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

Hi Bill,

 

I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

 

If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

 

Have a nice day!

 

 

Best Regards,
Lingzhi Sun

MSDN Subscriber Support in Forum

If you have any feedback on our support, please contact [e-mail]

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.