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. DevCraft banner
  4. 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.
  5. 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