Datacontext models with join

Question

I’m trying to get details about the business when I get a deal. I want to translate this query:

select d.*, b.* from deals d inner join business b
on b.id = d.businessId
where d.IsEvent = true

I tried it like this:

public List<Deal> GetEventsDeals()
{
    var deals = DataContext.Deals.Join(DataContext.Businesses,
        d => d.BusinessId,
        b => b.Id,
        (d, b) => new {Business = b, Deal = d})
        .Where(d => (d.Deal.IsEvent == true));

    return deals.OrderBy(d => d.Deal.Order).Take(50).ToList();
}

But I’m getting an error that I need to return List<AnnoymousType> and cannot convert it to List<Deal>.

How can I translate my query?

Answer 1

The error message tells you that you are trying to convert an anonymous type to a Deals type, which it doesn't know how to do. You need to do it this way for the code to work

public List<Deal> GetEventsDeals()
{
    var deals = DataContext.Deals.Join(DataContext.Businesses,
        d => d.BusinessId,
        b => b.Id,
        (d, b) => new Deal(){Business = b, Deal = d})
        .Where(d => (d.Deal.IsEvent == true));

    return deals.OrderBy(d => d.Deal.Order).Take(50).ToList();
}

note: as pointed out by Panagiotis Kanavos, this is not the way entityframework is designed to be used. Inner joins should be replace with navigation properties.

To do this properly you should have a model like so

public class Deal
{
    public int BusinessId { get; set; } 
    [ForeignKey("BusinessId")] // I believe this attribute is redundant because the names follow conventions, but you should check that
    public virtual Business Business { get; set; }
    public bool IsEvent {get;set;}
    public int Order {get;set;}
}

so you do your call as follow

var deals = DataContext.Deals.Include(d => d.Business).Where(d => d.Deal.IsEvent == true);

return deals.OrderBy(d => d.Deal.Order).Take(50).ToList();