Optimize Linq Query for Relations

[Manuel]

Dear all,

please have a look at this Linq Qyery:

DateTime start = new DateTime(2014,1,1);
DateTime end = new DateTime(2014,12,1);
Guid address = Guid.Parse("16CF8E4F-8735-4E2E-BFDF-2A2E0C816A3B");

var result =  from o in Orders
             join ol in OrderLines
             on o.Id equals ol.OrderId
             join ad in Addresses
             on o.AddressId equals ad.Id
where o.Date >= start && o.Date <= end && o.AddressId == address
group new { ol.ProductLine, ad.Name1, ol.Price }
by new {ol.ProductLine, ad.Name1  } into gr
select new
{
   ProductLine = gr.Key.ProductLine,
   Sum = gr.Sum(x => x.Price),
   Address = gr.Key.Name1
};

 

Between the Orders and the OrderLines I have defined a relation in the MetadataSource:

public void PrepareOrderRelationConfiguration(MappingConfiguration<Order> configuration)
{
// new Relation (One to Many)

configuration
.HasAssociation<OrderLine>(x => x.OrderLines)
.HasFieldName("OrderLines")
.WithOpposite(x => x.Order)
.ToColumn("OrderId")
.HasConstraint((y, x) =>  x.OrderId == y.Id)
.IsManaged()
.WithDataAccessKind(DataAccessKind.ReadWrite);

}

public void PrepareOrderLineRelationConfiguration(MappingConfiguration<OrderLine> configuration)
{
// new Relation (Many to One, Master)

configuration
.HasAssociation<Order>(x => x.Order)
.HasFieldName("Order")
.WithOpposite(x => x.OrderLines)
.ToColumn("OrderId")
.HasConstraint((x, y) =>  x.OrderId == y.Id)
.IsRequired()
.IsManaged()
.WithDataAccessKind(DataAccessKind.ReadWrite);

}

Relation in OrderLine class:

private Order Order;
[DataMember]
public virtual Order Order
{
    get
    {
        return Order;
    }
    set
    {
        if (Order != value)
        {
            Order = value;
            OnPropertyChanged();
        }
    }
}

private Guid OrderId;
[DataMember]
public Guid OrderId
{
    get { { return OrderId; } }
    set
    {
        if (OrderId != value)
        {
            OrderId = value;
            OnPropertyChanged();
        }
    }
}

 

Relation Order class:

private TrackedBindingList<OrderLine> OrderLines = new TrackedBindingList<OrderLine>();
        public virtual TrackedBindingList<OrderLine> OrderLines
        {
            get
            {
                return this.OrderLines;
            }
        }

 

Now my two questions:

Is the Join for OrderLines in the Linq Query necessary,  or can it be done in a better way?

I do not have a relation between Orders and Addresses (because I do not wanna have all orders when I query an address), do you recommend one?

Thank you,

Manuel

[Boyan]

Hi Manuel,

Looking into the attached query, it seems that the join to the Address table is placed to fulfill the requirement to select the Address.Name1 column. If this is indeed required, I think that adding the join is a good way to retrieve the value especially when you expect just just a single address (because of the AddressId filter).

To address your second question related with the association mapping, I think that there are two main consideration in such situations:

1. You may find it handy in certain scenarios to have a navigational property as abstraction over the database schema thus simplifying a bit your LINQ queries. Telerik Data Access loads the navigational properties in lazy fashion meaning that if such property is not accessed no additional queries will be fired against database. Weather to use navigational properties (as opposed to LINQ joins) in most cases is dictated by personal preferences. In either way, you should use our Profiler tool (or the SQL Server Profiler) to monitor the actual SQL statement executed against the database.

2. If you are using Telerik Data Access to update your database schema (as opposed to the database first approach), you should know that in case of an association between two entities an index on the Foreign Key is placed. This is not the case though if you do not define an association - then you need to decide if you need such index and add it manually. You should have that in mind as well when deciding to place mapping for association between those entities.

I hope this is helpful.

In case you have any further question do not hesitate to get back to us.

Regards,
Boyan
Telerik