LINQ query finding the minimum value of one column

Question

I have a table with the following columns

inspection_dt,
contact_dt,
description,
product_mod,
product_desc,
contact_nm,
history_id,
inspect_id,
history_type,
incident_product_id,
contact_history_id

I would to use LINQ to query a generic list of rows from this table. The twist is that I want the smallest (MIN) value of history_id -- and mimic this SQL query.

SELECT DISTINCT
    inspection_dt,
    contact_dt,
    description,
    product_mod,
    product_desc,
    contact_nm,
    MIN(history_id) AS history_id,
    inspect_id,
    history_type,
    incident_product_id,
    contact_history_id
FROM
    myTable
GROUP BY
    inspection_dt,
    contact_dt,
    description,
    product_mod,
    product_desc,
    contact_nm,
    inspect_id,
    history_type,
    incident_product_id,
    contact_history_id

I've tried snippets like

var searchData = items
    .GroupBy(i => new { i.history_id })
    .Select(g => new { history = g.Min() })
    .Distinct();

But still get all messed up

I get stuck using functions like MIN, MAX, etc, and grouping in LINQ and would appreciate any help I can get.

Thanks,

Anthony Pegram · Accepted Answer · 2011-01-07 17:55:05Z

4

If you want to mimic the query completely, you need to group by the same columns or fields you're grouping by in your SQL. Try something like

.GroupBy(item => 
      new 
      {
          item.inspection_dt,
          item.contact_dt,
          item.description,
          item.product_mod,
          item.product_desc,
          item.contact_nm,
          item.inspect_id,
          item.history_type,
          item.incident_product_id
      }
     )
.Select(g => g.Min(item => item.history_id))

answered Jan 7, 2011 at 17:55

Anthony Pegram

128k28 gold badges229 silver badges252 bronze badges

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

Comments

scartag · Accepted Answer · 2011-01-07 17:52:18Z

1

You could try this code below.

I noticed it generates an SQL cross join when i view it in profiler but i think it does what you want and you might be able to tweak it more.

var searchData = items.Select(x => new {x.inspection_dt,x.contact_dt, history= items.Min(j => j.history_id)}).Distinct();

Hope this helps

answered Jan 7, 2011 at 17:52

scartag

17.7k3 gold badges52 silver badges53 bronze badges

Collectives™ on Stack Overflow

LINQ query finding the minimum value of one column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related