none
For those experienced RIA developers, how are you handling complex queries?

    Question

  • Hello Everyone,

    I'm fairly new to silverlight and ria services, but I love em! One item that seems to be a bit clunkly is creating and returning complex objects. For instance, if I want to retrieve data from a table that is more than one table away, how would you handle that with a .Include? In a situation where I have a centralized table, say I'm selecting from customer, and I retrieve all customer orders through the fk, but also want to retrieve all of the stores in which the order was placed, so an fk off order, how do you pull that off? Certainely the customer isn't directly linking to the store, so the .Include statement is going to send me packing.

    What types of strategies are you guys using in these cases?

    Thanks a bunch everyone,

    Ryan

    Monday, September 06, 2010 9:31 PM

Answers

  • You can have multiple includes and each include can have an entire path. For example, you can put in .Include("Orders.Stores").

    However, in Entity Framework such an include will tend to have very bad performance as it is an include across a Many. The performance is within SQL Server and Entity Framework and has nothing to do with RIA Services. What I would do in that situation is load Orders instead of Customers. I would then do a .Include("Stores").Include("Customers") to load the related entities. In cases where you get even more multiples involved(.Include("Stores.Departments") then I use separate loads.

    Tuesday, September 07, 2010 4:03 PM

All replies

  • I can only add my humble efforts to this as I have a fairly complex project on the go at the moment and I have questions relating to this as well.

    I am loading lookup data into a static class on the application load.

    This uses a batch load function to load 10 lookup tables that are used in combo boxes etc in the application.

    The combo's are then bound to these lists whenever a form, or child form is shown and only get loaded once at startup.

    For complex data types I have created views in SQL and then imported these as entities, which allows you to load complex data directly from ria services without using includes.

    I too am searching for a way to create entities within the framework that are the same as the views, i.e. include only some fields from multiple tables linked together. At  present I have hit a dead end, but maybe someone else can join in and point a few resources out?

    Regards

    Rupert

    Tuesday, September 07, 2010 1:09 PM
  • You can have multiple includes and each include can have an entire path. For example, you can put in .Include("Orders.Stores").

    However, in Entity Framework such an include will tend to have very bad performance as it is an include across a Many. The performance is within SQL Server and Entity Framework and has nothing to do with RIA Services. What I would do in that situation is load Orders instead of Customers. I would then do a .Include("Stores").Include("Customers") to load the related entities. In cases where you get even more multiples involved(.Include("Stores.Departments") then I use separate loads.

    Tuesday, September 07, 2010 4:03 PM
  • Thanks for the responses guys. I've been somewhat leary of using views simply because querying against a view with say, additional where clauses, always had such poor performance in sql. Are you experiencing any of that, or is linq generating the appropriate query to minimize this?

    Colin, I'm sure you're right, having multiple includes has to hammer performance. I sometimes see odd results with includes that you might be able to offer something on. From time to time, say on a self referencing table, my include won't work. I'll use the include in both the query, as well as the meta data object reference, yet it'll never return the given record. For instance if I have a customer table, that fk's to itself for customer referrals, I can't talk the include into returning that associated customer object. Have you seen issues like this previously?

     

    Thanks again too you both,

    Ryan

    Tuesday, September 07, 2010 4:16 PM
  • The reason I use a view is exactly what Colin is pointing out and an issue I'm still trying to resolve in a more appropriate way, which is at least in a view you control exactly the amount of data returned, rather than simply joining together whole entities and returning the whole shebang.

    I have to date found using views extremely efficient as it is only ever called with filters, rather than any joins, the point being that the view already has all the data you need to display in the form you want it!

    PS I am just looking at linqtosql as it is possible to query the data directly and return linked tables with only the fields required. This then of course needs to be hooked up to the service etc, but would appear to be an allternative.

     

    Regards

    Rupert 

    Wednesday, September 08, 2010 1:06 PM
  • Hello Colin

     

    I never seen a good example for separe/split loads. Is there any I can check?

     

    Wednesday, March 23, 2011 10:36 AM
  • I am not sure what you are asking for. It just works in RIA Services so there isn't really anything to demo. But, I will give it a try.

    So, given the query:

    return ObjectContext.Persons.Include("Address").Where(p => p.Id == PersonId);

    You can have to separate query methods:

    return ObjectContext.Persons.Where(p => p.Id == PersonId);

    and

    return ObjectContext.Addresses.Where(a => a.PersonId == PersonId);

    Once both of those loads has completed, the result in the DomainContext would be identicle to the first query.

    Wednesday, March 23, 2011 10:59 AM