LINQ query finding the minimum value of one column

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 DISTI开发者_StackOverflow中文版NCT
    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,

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))

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