How to specify condition on joined entities

[Vladimir]

Hello,
I would like to write following SQL statement in LINQ. Is there any way how to achive it in OpenAccess?

(Let's assume I have two classes in my model - Order and OrderItem. A class Order has navigation property - collection of OrderItems)

SELECT * FROM Orders AS o
LEFT JOIN OrderItems AS oi ON (oi.OrderId = o.Id AND oi.State = 3)
WHERE o.UserId = 2

The point is to have only Orders for given user and, moreover, in navigation properties of these orders, only order items having State == 3 should be included.  

Could such a query be written via LINQ in OpenAccess? Thanks for any advice.

[Doroteya]

Hi Vladimir,

As discussed in the support thread, in order to solve the problem, two approaches were suggested:

1) Using the Low Level (ADO) API
Since Telerik OpenAccess ORM currently supports only the typical join operator, the execution of left join queries is available thanks to the Low Level ADO API. It offers means to set up the necessary select statement and its parameters, and to execute it via the ExecuteQuery<T>() method of OpenAccessContext. The data retrieved from the database, could be materialized to either an anonymous or a strongly typed result set (step-by-step description of the approach here).

The following code snippet demonstrates the approach. It uses the Northwind database and the result from the query is materialized to a predefined Result class.

using (EntitiesModel dbContext = new EntitiesModel())
{
    using (OAConnection oaConnection = dbContext.Connection)
    {
        string SqlQueryString =  @"SELECT * FROM Orders AS o" +
                      @" LEFT JOIN [Order Details] AS oi ON " +
                      @"(oi.OrderID = o.OrderID " +
                      @"AND oi.ProductID = @product) " +
                      @"WHERE o.EmployeeID = @emp";
        using (OACommand oaCommand = oaConnection.CreateCommand())
        {
            OAParameter[] oaParams = new[]
                {
                    new OAParameter
                    {
                        ParameterName = @"product",
                        DbType = System.Data.DbType.Int32,
                        Value = 1
                    },
                    new OAParameter
                    {
                        ParameterName = @"emp",
                        DbType = System.Data.DbType.Int32,
                        Value = 1
                    }
                };
            IEnumerable<Result> carDetails = dbContext.
                ExecuteQuery<Result>(SqlQueryString, oaParams);
        }
    }
}

The definition of the Result class:

public class Result
{
    public int OrderID { get; set; }
    public string CustomerID { get; set; }
    public int? EmployeeID { get; set; }
    public DateTime? OrderDate { get; set; }
    public DateTime? RequiredDate { get; set; }
    public DateTime? ShippedDate { get; set; }
    public int? ShipVia { get; set; }
    public decimal? Freight { get; set; }
    public string ShipName { get; set; }
    public string ShipAddress { get; set; }
    public string ShipCity { get; set; }
    public string ShipRegion { get; set; }
    public string ShipPostalCode { get; set; }
    public string ShipCountry { get; set; }
    public int ProductID { get; set; }
    public decimal UnitPrice { get; set; }
    public short Quantity { get; set; }
    public float Discount { get; set; }
}

Note: The result set in this case would be read-only and cannot be persisted in the database after the changes you might want to make.

2) LINQ Statement and Grouping
The solution utilizes your database schema and selects from the OrderItems table, filters the result according to some criteria and then groups the result by the returned Orders. The result is materialized to an anonymous type where the key from the grouping is cast to Orders and the result itself is assigned to a list of OrderItems.

using (EntitiesModel dbContext = new EntitiesModel())
{
    FetchStrategy fetchStrategy = new FetchStrategy();
    fetchStrategy.LoadWith<OrderItem>(o => o.Order);

    dbContext.FetchStrategy = fetchStrategy;

    var result = (from oi in dbContext.OrderItems
                where oi.State == 1 && oi.Order.UserID == 1
                group oi by oi.Order into g
                select new
                {
                    Order = g.Key,
                    Items = g
                }).ToList();
}

Note: In order to reduce the number of the queries executed against the database, the solution involves the usage of our FetchStrategy class. What it basically does is to retrieve not only the requested object but also the objects related to it. More on the subject is available here.

If you have any other questions, do not hesitate to get back to us.


Regards,
Doroteya
the Telerik team