[Solved] FetchPlan and IList

[Andrey]

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.

[Thomas]

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

[Andrey]

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.

[Thomas]

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