Optimize Linq Query for Relations

2 posts, 0 answers
  1. Manuel
    Manuel avatar
    43 posts
    Member since:
    Jul 2010

    Posted 07 Dec 2015 Link to this post

    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

  2. Boyan
    Admin
    Boyan avatar
    100 posts

    Posted 10 Dec 2015 Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  3. DevCraft banner
Back to Top