locked
Join across mutliple entity data sources RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    I'm trying to limit the rows returned by a "Parent" entity data source to only display the parent record if there are no matching records in the "Child" entity data source (much like an INNER JOIN in T-SQL). I know that I could use the LINQ to Entities to create an IEnumerable object this way, but the existing solution uses data sources, and i'd like to avoid rewriting the entire thing.

    Here is my scenario in brief -
    I have two Grid Views (in this case, I am using Devexpress's ASPxGridView control and performing a Master / Detail grid setup. That, so far, is functioning as advertised. If you need more information, you can check out the Devexpress site.)

    I can model this using the classic Northwind structure.

    I have a Parent grid that displays Categories, and a Child grid that displays Products. The categories grid populates using a categories entity data source, and the products grid populates using a products entity data source. The product entity data source is filtered by a where clause that uses the "CategoryID" session variable. When the child row of the categories grid is expanded, I set the CategoryID session field to the selected CategoryID, and then the products data source only retrieves the products in that category. So far, so good... everything there works fine.

    Well, the next step is to create a set of filters on the page that allow the user to search detailed information, and filter the grid before the data is selected. For instance, they would want to see all products that start with "A". Normally, that would be easy, however, I only want to display the Categories that also have a Product that start with the letter A, and not just EVERY category.

    I've investigated the Entity SQL language, but I can't use anything like (from the Category data source perspective) "it.Product.Name LIKE '%A%'".

    If anyone has any insight, or if i'm just really missing something silly and easy, let me know.
    Thanks!
    Rudy Kroska - Analyst, MCSD.NET, MCT
    Monday, March 16, 2009 9:46 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    I think what you want is the exists function in entity sql.  If I understand you correctly, you want to filter on categories that start with a certain letter and then further eliminate any categories that don't have any related products.  The way you would do this is to take advantage of exists plus navigation properties.  So you would make the where parameter on the entity data source something like:

     

         it.CategoryName LIKE '%A%' AND exists(it.Products)

     

    This will automatically create the appropriate join in order to navigate from the category to the related products and then use that join to filter the categories on those where the collection of related products is not empty.

     

    - Danny

    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, April 1, 2009 3:05 AM