locked
EntityDataSource - Appropriate Way to Specify Records to Load RRS feed

  • Question

  • Problem 1: Filtering EntityDatasource

    I have various controls that load from an EntityDataSource. What I want to do is be able to display only certain records, depending on what has taken place in the application. Right now I just set the .Where statement to restrict the records returned to only those I want to display. However, I'm not sure how to specify multiple things (i.e. Where-In).

    For instance, say I have an array of IDs (1, 2, 3, ...) and I want to say "Where IDs are in the array"?

    // Something like this, but of course this isn't right.
    edsTblA.Where = @"it.[ID] In " + CustIDs[];

    Problem 2: Timing of Events

    I have the data source filter based on what row in a GridView is selected. The problem is that the edsTblA.Load fires before GridView1.OnRowCommand. So, how am I supposed to tell the EntityDataSource what to filter on when I don't know yet?


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Tuesday, August 3, 2010 4:59 PM

Answers

  • Okay, so I've taken a better look at my LINQ statements. Please pardon me if they're not truely LINQ. I'm just learning this LINQ-type stuff and generics, so I am still kind of sketchy about exactly what's LINQ vs Lambda vs whatever else.

    So, instead of doing something like this pseudo code (which is FAR from real code).

    myEDS.Where = @"it.[ID] In " + myArrayOfIDs[];

    I did this.

    List<int> myIDs = (from c in db.tbl_Customers
                   where c.State == "OR"
                   select c.CustomerID).ToList();
    foreach (int i in myIDs)
    {
       myEDS.WhereParameters.Add("CustomerID", SqlTypes.Int32, i.ToString());
    }

    I haven't had a chance to fully test it yet, so I hope it works. However, it's much closer to what I wanted to do. I hope this helps others!


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    • Marked as answer by Nathon Dalton Wednesday, August 11, 2010 9:59 PM
    Wednesday, August 11, 2010 9:59 PM

All replies

  • For problem 1 I think the following should work.

     

    int[] MyIDs = {1, 2, 3, 4, 5};

    var MyItems = from Item in dataEntities.Items
                          where MyIDs.Contains(Item.ItemID)
                          select Item;

    Tuesday, August 3, 2010 10:23 PM
  • risc,

    The problem is that I'm connecting directly to an EntityDataSource object in the designer. So, I need to modify the EDS object to determine what I want. I'm using that because it automatically takes care of CRUD operations through my Entity Data Model for me. Saves me a lot of coding. So, the solution I'm looking for will modify the EDS somehow to determine what records to load or will in some way leave that relationship intact so that CRUD operations will continue to work properly. Thanks!


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Wednesday, August 4, 2010 12:27 AM
  • Ahh ok, I understand what you mean. Unfortunately I don't know how to solve it, atleast not by only modifying the EDS. Haven't tried this but maybe you can do further filtering (see link), and still keep your CRUD-stuff.

    http://msdn.microsoft.com/en-us/library/ee404748.aspx

     

    Monday, August 9, 2010 9:06 PM
  • Okay, so I've taken a better look at my LINQ statements. Please pardon me if they're not truely LINQ. I'm just learning this LINQ-type stuff and generics, so I am still kind of sketchy about exactly what's LINQ vs Lambda vs whatever else.

    So, instead of doing something like this pseudo code (which is FAR from real code).

    myEDS.Where = @"it.[ID] In " + myArrayOfIDs[];

    I did this.

    List<int> myIDs = (from c in db.tbl_Customers
                   where c.State == "OR"
                   select c.CustomerID).ToList();
    foreach (int i in myIDs)
    {
       myEDS.WhereParameters.Add("CustomerID", SqlTypes.Int32, i.ToString());
    }

    I haven't had a chance to fully test it yet, so I hope it works. However, it's much closer to what I wanted to do. I hope this helps others!


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    • Marked as answer by Nathon Dalton Wednesday, August 11, 2010 9:59 PM
    Wednesday, August 11, 2010 9:59 PM
  • Hello Nathon,

    It sounds like the solution you describe should work as long as you set AutoGenerateWhereClause = true.

    In any case, if you are using the new version of the EntityDataSource in .NET 4.0, I think you can implement a solution that in my opinion is simpler, based on the QueryCreated event. The QueryCreated event allows you to modify the query of the data source (i.e. using LINQ) before it is executed. The following section in the documentation explains how to do it in more details: http://msdn.microsoft.com/en-us/library/ee404748.aspx, but here is a short example (based on the description of the problem above):

    On the aspx page:
    <asp:EntityDataSource ID="EntityDataSource1" runat="server"
        ConnectionString="name=SomeConnection"
        DefaultContainerName="SomeContainer"  
        EntitySetName="Orders"
        onquerycreated="EntityDataSource1_QueryCreated">
    </asp:EntityDataSource>
    On the code-behind file:

    protected void EntityDataSource1_QueryCreated(object sender, QueryCreatedEventArgs e)
    {
        var orders = e.Query as ObjectQuery<Orders>;
        var context = orders.Context;
        var customerIds =
            (from c in context.Customers
             where c.State == "OR"
             select c.CustomerId).ToList();
        e.Query =
            from o in orders
            where customerIds.Contains(o.CustomerId)
            select order; 
    }

    Hope this helps,
    Diego


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, August 16, 2010 3:22 AM
  • Diego,

    Yes, I'm using the .NET Framework 4.0. So, the .Contains() will work against a List of items? That's great! I'm already using a List to contain the IDs of the related records. This way I can just do a .Contains(MyListOfIDs).


    Nathon Dalton
    Software Developer
    Systems Administrator
    Network Administrator
    Blog: http://nathondalton.wordpress.com
    Wednesday, August 18, 2010 6:04 PM