c# LINQ to dataset IN clause contains, group, min

Question

I am new to LINQ but am trying to tackle a tough one right off the bat. I am trying to do LINQ to dataset and emulate the following query...

SELECT smID, MIN(entID) FROM table
WHERE exID = :exID
AND smID IN (1,2,3,4,5,6,7,8, etc)
GROUP BY smID

The code I have so far is as follows...

DataTable dt = ds.Tables["myTable"];

var query =
    from g in dt.AsEnumerable()
    where g.Field<string>("exID") == exID
    && smIDs.Contains(g.Field<string>("smID"))
    group g by g.Field<string>("smID") into rowGroup
    select new
    {
        smID = rowGroup.Key,
        minEntID = rowGroup.Min(g => g.Field<int>("entID"))
    };

exID is a string variable in the method and smIDs is a List of strings also created earlier in the method. I created the following code to try and see my results and it throws an "System.InvalidCastException" error at query.Count...

             if (query.Count() > 0)
             {
                 foreach (var item in query)
                 {
                     string s = item.smID;
                     int i = (int)item.minEntID;
                 }
             }

I have been unable to figure out what I am doing wrong.

VS points to...

minEntID = rowGroup.Min(g => g.Field<int>("entID"))

This is the first two lines of the stack trace...

   at System.Data.DataRowExtensions.UnboxT`1.ValueField(Object value)
   at System.Data.DataRowExtensions.Field[T](DataRow row, String columnName)

Any pointers would be most appreciated. Thanks.

Also, you could just write `…group g by g.Field("smID")…`. — Marcelo Cantos, Dec 13 '11 at 23:54
Is smID a string or int in the data table? In your original query, it looks like an int, but in your LINQ query you're using strings. — Collin K, Dec 14 '11 at 00:19
@CollinK - technically it is a string. It will never be such but the DB stores it as a varchar and we carried that up into the dataset definition. — John, Dec 14 '11 at 00:27
@MarceloCantos - thanks for the tip about eliminating the new in grouping. Not sure where I saw that earlier. I changed it in my code. — John, Dec 14 '11 at 00:42

score 1 · Accepted Answer · answered Dec 14 '11 at 01:21

1

Judging by the exception and stack trace, the type you're specifying for the endID field in your query doesn't match the DataType for that column in the DataTable. These must match -- you cannot use the Field method to cast the value to a different type.

answered Dec 14 '11 at 01:21

Collin K

15,277
1
27
22

You are correct. I changed 'code' minEntID = rowGroup.Min(g => g.Field("entID")) to 'code' minEntID = rowGroup.Min(g => Utilities.ConvertFieldtoInt(g.Field – John Dec 14 '11 at 14:36

jklemmack · Answer 2 · 2011-12-14T01:03:33.337

0

I used Linqer to come up with this code:

from t in db.Table // your C# table / collection here, of course
where t.ExId == stackoverflow.ExId 
    && (new int[] {1, 2, 3 }).Contains(t.SmId)
group t by new { t.SmId } into g
select new {
   SmId = g.Key.SmId,
   minEntID = g.Min(p => p.EntId)
}

edited Dec 14 '11 at 01:03

answered Dec 14 '11 at 00:58

jklemmack

3,518
3
30
56

c# LINQ to dataset IN clause contains, group, min

2 Answers2