FetchPlan and IList

4 posts, 0 answers
  1. Andrey
    Andrey avatar
    21 posts
    Member since:
    Aug 2010

    Posted 20 Jun 2013 Link to this post

    Hello!
    I use Forward mapping to Firebird database
    Telerik ORM v. 2010
    I try to use FetchPlan for a class with IList property:

    [Persistent(IdentityField = "nr")]
    public class Group
    {
        [FetchField("fgGroupFull")]
        private int nr;
     
        [FetchField("fgGroupFull")]
        private string name;
     
        [FetchField("fgGroupFull", Next = "fgItemNr")]
        private IList<Item> items;
     
        public int Nr
        {
            get { return nr; }
            set { nr = value; }
        }
     
        public string Name
        {
            get { return name; }
            set { name = value; }
        }
     
        public IList<Item> Items
        {
            get { return items; }
            set { items = value; }
        }
    }
     
    [Persistent(IdentityField = "nr")]
    public class Items
    {
        [FetchField("fgItemNr")]
        private int nr;
     
        private string name;
     
        public int Nr
        {
            get { return nr; }
            set { nr = value; }
        }
     
        public string Name
        {
            get { return name; }
            set { name = value; }
        }
    }

    so as you see I have a class "Group" with IList of class "Item"
    After mapping these classes to a database I have 3 tables:
    [GROUP_]
    [ITEM_]
    [GROUP_ITEM] - table for link "Groups" and "Items"

    I need to get from a database all columns for "Groups" and only "Nr" for List of "Items". That's why I use FetchField attributes
    I expect that when I get "Group" list from scope it will be executed only one sql-query like this:
    select
      g.NR,
      g.NAME_,
      i.NR
    from
      GROUP_ g,
      ITEM_ i,
      GROUP_ITEM gi
    where (g.NR = gi.GROUP_NR and i.NR = gi.ITEM_NR)

    But when I get a list of "Groups" - it is getted only a list of "Groups" without list of "Items".
    And when I try to access to Group.Items - now will executed query and all "Items" for current "Group" will be getted.
    I have many "Groups" and many "Items" in each "Group"
    So instead only one query it is executed hundreds of sql-queries to get list of "Items" for each (more than 100) "Group".

    How I can optimize my Fetch Plan to execute only one query?

    Thanks.
  2. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 21 Jun 2013 Link to this post

    First of all: Please use a newer version, it's free.

    Second: Please set the fetch plan to include the fgGroupFull before executing the linq query that delivers the groups, or use LoadWith(g => g.Items.Select(i => i.Nr)) in the LINQ query.

    Best regards,
    Thomas
    Telerik
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
  3. Andrey
    Andrey avatar
    21 posts
    Member since:
    Aug 2010

    Posted 26 Jun 2013 Link to this post

    I add fgGroupFull and use it already:
    scope.FetchPlan.Remove("default");
    scope.FetchPlan.Add("fgGroupFull");
    try
    {
        return scope.Extent<Group>().ToList();
    }
    finally
    {
        scope.FetchPlan.Add("default");
        scope.FetchPlan.Remove("fgGroupFull");  
    }

    But it doesn't solve the problem.
  4. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 27 Jun 2013 Link to this post

    Sorry, I gave you a misleading answer :-(

    There is currently only one way to achieve this query:

    scope.Extent<Group>().SelectMany(x => x.Items, (g,i) => new { GroupName = g.Name, GroupId = g.Id, ItemId = i.Nr }).ToList();

    This projection will just perform the join needed, but you will get back a projection, so updates are not possible; but it is slightly faster too, as we dont need to handle the OIDs/PersistenceCapables in memory.

    If you can life with two queries this could be achieved with:

    scope.FetchPlan.Remove("default");
    scope.FetchPlan.Add("fgGroupFull");
    try
    {
         return scope.Extent<Group>().ParallelFetch(true).ToList();
    }
    finally
    {
     scope.FetchPlan.Add("default");
     scope.FetchPlan.Remove("fgGroupFull");  
    }
    This wll generate one query for the group instances, and a second correlated query for the item instances. The returned instances are updatable.


    Regards,
    Thomas
    Telerik
    OpenAccess ORM Q2 2013 brings you a more powerful code generation and a unique Bulk Operations support with LINQ syntax. Check out the list of new functionality and improvements shipped with this release.
Back to Top