LINQ to Entities with left join

Question

I have made my query to the database as raw SQL and want to shift to SQL to entities.

Reason for that is performance is terrible in the way it's constructed, not the actual query, which is fast, but afterwards I had to perform multiple SQL statements to get data from associated tables. I was thinking that if I moved to SQL to entities, the associated tables were already fetched and I only had to make that one call to the database.

I construct my old query like this:

List<Database.Product> products = null;
string sql = "SELECT P.* FROM Product p " +
             "LEFT JOIN ProductAssigned pa ON pa.ProductId = p.Id " +
             "WHERE pa.UserId = @userid AND pa.UnassignedAt IS NULL";

sql = FilterByProductState(productFilter, sql);

if (startDate != default && endDate != default)
{
    string start = startDate.Year + "/" + startDate.Month + "/" + startDate.Day;
    string end = endDate.Year + "/" + endDate.Month + "/" + endDate.AddDays(1).Day;
    sql += " AND (p.StartAt BETWEEN '" + start + "' AND '" + end + "' OR p.CompleteAt BETWEEN '" + start + "' AND '" + end + "')";
}

List<SqlParameter> sqlParameters = new List<SqlParameter>
{
    new SqlParameter("@userid", userId)
};

sql += " ORDER BY p.StartAt";

try
{
    products = context.Database.SqlQuery<Database.Product>(sql, new SqlParameter("@userid", userId)).ToList();
}
catch (Exception e)
{
    throw;
}

The missing method from the code:

private static string FilterByProductState(ProductFilter productFilter, string sql)
{
    switch (productFilter)
    {
        case ProductFilter.Started:
            sql += " AND p.StartedAt IS NOT NULL";
            sql += " AND p.CompletedAt IS NULL";
            sql += " AND p.DeletedAt IS NULL";
            break;
        case ProductFilter.Closed:
            sql += " AND p.CompletedAt IS NOT NULL";
            sql += " AND p.DeletedAt IS NULL";
            break;
        case ProductFilter.AllNotStarted:
            sql += " AND p.StartedAt IS NULL";
            sql += " AND p.DeletedAt IS NULL";
            break;
        case ProductFilter.All:
            default:
            break;
    }
    return sql;
}

After this like I said I have to go and get certain associated tables.

I have tried to start several things, e.g. using GroupJoin, but nothing seems to pan out. Also I'm creating the SQL statement dynamically, so I don't even know if it could work in SQL to entities.

UPDATE:

Product(the interesting parts) and ProductAssigned is shown here:

public partial class Product
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Product()
    {
        this.ProductAssigned = new HashSet<ProductAssigned>();
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public Nullable<System.DateTime> StartAt { get; set; }
    public Nullable<System.DateTime> CompleteAt { get; set; }
    public Nullable<System.DateTime> DeletedAt { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<ProductAssigned> ProductAssigned { get; set; }
}


public partial class ProductAssigned
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int UserId { get; set; }
    public System.DateTime AssignedAt { get; set; }
    public Nullable<System.DateTime> UnassignedAt { get; set; }

    public virtual Product Product { get; set; }
    public virtual User CreatedByUser { get; set; }
    public virtual User DeletedByUser { get; set; }
    public virtual User UserAssigned { get; set; }
}

Answer 1

The LINQ query can be built in a very similar fashion. Just instead of manual joins, in EF queries you think in objects, references and collections, and EF converts that to the necessary joins.

The first part could be written exactly as the SQL query

IQueryable<Database.Product> query =
    from p in context.Products
    from pa in p.ProductAssigned.DefaultIfEmpty() // <-- left join
    where pa.UserId == userid && pa.UnassignedAt == null
    select p;

but it doesn't make much sense. WHERE condition after LEFT JOIN effectively makes it INNER, and also the whole purpose of that join in the original query seems to be to check for existence, so it could be formulated as follows

IQueryable<Database.Product> query = context.Products
    .Where(p => p.ProductAssigned.Any(pa =>
        pa.UserId == userid && pa.UnassignedAt == null));

Whatever you choose for the first (static) part of the query, the rest of it (the dynamic part) can be built by simply chaining Where calls (at the end they will be combined with AND in the WHERE clause).

query = FilterByProductState(productFilter, query);

if (startDate != default && endDate != default)
{
    var start = startDate.Date;
    var end = start.AddDays(1);
    query = query.Where(p => (p.StartAt >= start && p.StartAt <= end)
        || (p.CompleteAt >= start && p.CompleteAt <= end));
}

query = query.OrderBy(p => p.StartAt);

and the equivalent helper method

private static IQueryable<Database.Product> FilterByProductState(
    ProductFilter productFilter,
    IQueryable<Database.Product> query)
{
    switch (productFilter)
    {
        case ProductFilter.Started:
            query = query.Where(p => p.StartedAt != null
                && p.CompletedAt == null
                && p.DeletedAt == null);
            break;
        case ProductFilter.Closed:
            query = query.Where(p => p.CompletedAt != null
                && p.DeletedAt == null);
            break;
        case ProductFilter.AllNotStarted:
            query = query.Where(p => p.StartedAt == null
                && p.DeletedAt == null);
            break;
        case ProductFilter.All:
            default:
            break;
    }
    return query;
}