none
Load multiple related Tables RRS feed

  • Question

  • Hi.

    I would like to include several tables in a single query.

    My current code looks like this.

    var returnList = from x in entityModel.TabElements
                                .Include(y => y.TabElementAccounts.Select(z => z.TabAccount))
                        select x;

    This loads all "TabElements" objects with the many to many related "TabAccounts", now i like to extend this linq to load tables related to the "TabAccounts", with one addition its no problem i just put another .Select at the end of the methodchain something like:

    var returnList = from x in entityModel.TabElements
                                .Include(y => y.TabElementAccounts.Select(z => z.TabAccount).Select(z => z.TabAccountParent))
                        select x;
    But.... the next .Select gives the only the choices to load data related to the "TabAccountParent" this isnt what I desire. I wish to load several properties from with in the "TabAccounts". Tried it with a new expression like this:
    var returnList = from x in entityModel.TabElements
                                .Include(y => y.TabElementAccounts.Select(z => z.TabAccount).Select(z => new
                                    {
                                        z.TabAdjustAccounts,
                                        z.TabAccountParent
                                    }))
                        select x;

    But this code throws an exception:

    "The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties.
    Parametername: path"

    Hope you can help me.

    Thx in advance.


    • Edited by Rand.Random Tuesday, June 18, 2013 12:01 PM
    Tuesday, June 18, 2013 11:59 AM

All replies

  • Does the following work?

    var returnList = from x in entityModel.TabElements
                                .Include("TabElementAccounts.TabAccount.TabAccountParent")
           .Include("TabElementAccounts.TabAccount.TabAdjustAccounts")
                        select x;

    Tuesday, June 18, 2013 12:35 PM
  • More or less, with this simple query I am not getting an exception but when I like to go into depths it throws this error:

    Dynamic SQL Error
    SQL error code = -104
    Token unknown - line 172, column 2
    OUTER

    The code that throws this error looks like this:

    var returnList = (from x in entityModel.TabElements
                .Include("TabElementAccounts.TabAccount.TabAccountParent")
                .Include("TabElementAccounts.TabAccount.TabAdjustAccounts")
                .Include("TabElementAccounts.TabAccount.TabAccountAmounts")
                .Include("TabElementAccounts.TabAccount.TabAdjustAccounts.TabAdjust.TabAdjustType.TabAdjustTypeLangs")
                select x).ToList();
     

    I kinda have the feeling that once i specified a third include this way, it throws this sql error.

    I am using FirebirdSQL, should a try the same code with MSSQL or isnt it related to the SQL Server I am using?

    And a little question about this code, dont I need to specify it like this:

    var returnList = from x in entityModel.TabElements
                            .Include("TabElementAccounts")
                            .Include("TabElementAccounts.TabAccount")
                            .Include("TabElementAccounts.TabAccount.TabAccountParent")
                            .Include("TabElementAccounts.TabAccount.TabAdjustAccounts")
                        select x;

    Is it really enough to specify the most outer property of the "chain"?

    Also I'd like to know how this code would look like without the strings but with lambda.

    Tuesday, June 18, 2013 12:52 PM
  • Why don't you just join the tables if you need to load all related entites?

    var returnList = (from x in entityModel.TabElements
      join y in entityModel.TabAccountParent on x.ColumnId equals y.ColumnId
      ....
                select x).ToList();

    Tuesday, June 18, 2013 2:39 PM
  • This seems rather hard to accomplish, not only do I need to specify what Property shall be joined with what.

    I also need to make sure that all "TabElement" are loaded with "left joins", wich looks like this according to C# Linq Sample on MSDN (http://msdn.microsoft.com/en-us/library/vstudio/bb397895.aspx).

    join pet in pets on person equals pet.Owner into gj
                            from subpet in gj.DefaultIfEmpty()
    

    What I have done now and to me it looks nicer and you dont need to specify join column/properties and no "left join" is needed, I am stepping threw my list of TabElements and override the TabAccount property, this looks like this:

    tabElements = (from x in entityModel.TabElements
                                .Include(y => y.TabElementAccounts)
                                .Include(y => y.TabElementAlts)
                                .Include(y => y.TabElementChildren)
                                .Include(y => y.TabElementLangConditions)
                                .Include(y => y.TabElementLangs)
                                .Include(y => y.TabElementParent)
                                .Include(y => y.TabMapElementChildren)
                                .Include(y => y.TabMapElementParents)
                                .Include(y => y.TabSection)
                                .Include(y => y.TabStatAccounts)
                                .Include(y => y.TabStructure)
    
                    select x).ToList();
    
    foreach (var tabElement in tabElements)
    {
        foreach (var tabElementAccount in tabElement.TabElementAccounts)
        {
            tabElementAccount.TabAccount = (from x in entityModel.TabAccounts
                                                        .Include(y => y.TabAccountAmounts)
                                                        .Include(y => y.TabAccountChildren)
                                                        .Include(y => y.TabAccountLangs.Select(z => z.TabLang))
                                                        .Include(y => y.TabAccountParent)
                                                        .Include(y => y.TabAdjustAccounts.Select(z => z.TabAdjust).Select(z => z.TabAdjustType).Select(z => z.TabAdjustTypeLangs))
                                                        .Include(y => y.TabCompany)
                                                        .Include(y => y.TabElementAccounts)
                                            where x.IdAccount == tabElementAccount.IdAccount
                                            select x).FirstOrDefault();
        }
    }

    If I am seeing/understanding something wrong and there is an easier way to do this, I would be happy to read it from you, but for now I think looping threw the elements is the easiest way.

    Wednesday, June 19, 2013 8:52 AM
  • Any thoughts on my code that you like to share?
    Thursday, June 27, 2013 1:17 PM