c# - how to select columns from two table with where using linq

Question

I have three tables:

service_group
{
    id,
    title
}

service
{
    id,
    title,
    group_id
}

service_rate
{
    id,
    title,
    service_id,
    price,
    date
}

I have a combobox for group_service and user must select one and then open a form and user could have a choice from service_rate list. service_rate list includes all servic_rate with service_id in service.id which selected service_group_id.

Excuse me if I don't speak English well.

My code:

var list = (
  from p in db.Tbl_Services
  where p.Service_Group_ID == _service_group_id
  select new {
    p.ID,
    p.Title,
    p.Tbl_Services_Rate.Where(m = > m.Service_ID == p.ID).Last().Price,
    p.Tbl_Services_Rate.Where(m = > m.Service_ID == p.ID).Last().date
}).ToList();

but doesn't seem to work.

标签： c#entity-frameworklinqlambdalinq-to-sql

Answer 1

You can do something like this:

var list = db.Tbl_Services_Rate
    .Where(x=>x.service.group_id  == _service_group_id && x.service_id != null && x.service_id != "")
    .GroupBy(x=>x.service_id)
    .Select(x=>x.OrderByDescending(y=>y.id).FirstOrDefault())
    .ToList();

Null check is because you will remove those records that may contain service_id as null or empty since you will have to check by using this id.