none
Keeping track of composite objects across Multiple Joins RRS feed

  • Question

  • I am using Linq to SQL to generate dynamic SQL for an advanced search section of an application.  The idea is that the generated SQL will only join into the required tables for any requested search and that joins and where clauses can be added without knowledge of previous joins and filters that have been applied.

    To accomodate this I have created a search class to manage the joins and execute the query.  The search class looks something like this:

    public class Search
    {
      private IQueryable<T> Query { get; set; }

      public Search()
      {
        Query = from oValueA in DataContext.TableA
                select new Result() { ValueA = oValueA };
      }

      public void JoinIntoTableB()
      {
        IQueryable<TableBClass> oTableBQuery = from oValueB in DataContext.TableB select oValueB;
        Query = Query.Join(oTableBQuery, oResult => oResult.ValueA.ID, oValueB => oValueB.ID, (oResult, oValueB) => new Result() { ValueA = oResult.ValueA, ValueB = oValueB });
      }

      public void JoinIntoTableC()
      {
        IQueryable<TableCClass> oTableCQuery = from oValueC in DataContext.TableC select oValueC;
        Query = Query.Join(oTableCQuery, oResult => oResult.ValueB.ID, oValueC => oValueC.ID, (oResult, oValueC) => new Result() { ValueB = oResult.ValueB, ValueC = oValueC });
      }

      ...
    }

    public class Result
    {

      public TableAClass ValueA { get; set; }
      public TableBClass ValueB { get; set; }
      public TableCClass ValueC { get; set; }

    }

    The problem I have is finding a central way of keeping track of ValueA, ValueB and ValueC etc as the number of joins increases.  In the code above, the previous value for ValueA is lost when JoinIntoTableC() is called and the previous value for ValueC is lost when JoinIntoTableB() is called.  I can get around this problem by adding the missing assignments in the join clauses as follows:

    public void JoinIntoTableB()
      {
        IQueryable<TableBClass> oTableBQuery = from oValueB in DataContext.TableB select oValueB;
        Query = Query.Join(oTableBQuery, oResult => oResult.ValueA.ID, oValueB => oValueB.ID, (oResult, oValueB) => new Result() { ValueA = oResult.ValueA, ValueB = oValueB, ValueC = oResult.ValueC });
      }

    public void JoinIntoTableC()
      {
        IQueryable<TableCClass> oTableCQuery = from oValueC in DataContext.TableC select oValueC;
        Query = Query.Join(oTableCQuery, oResult => oResult.ValueB.ID, oValueC => oValueC.ID, (oResult, oValueC) => new Result() { ValueA = oResult.ValueA, ValueB = oResult.ValueB, ValueC = oValueC });
      }

    The problem with this method is that when I want to join into TableD and store ValueD in Result I have to go back and edit the joins for table B and C.  This is not a problem while there are a small number of joins availble in the Search class but becomes more of a pain when a large number of joins are available.  What I'm after is a way of keeping all the existing values from oResult in a join and just assigning the new value from the table being joined into.  Effectively I want to assign all the values between the previous Result class and the new Result class in one place and then overwrite the appropriate value with that from the join.

    Has anyone done anything similar?

    Thanks

    Rich

    Thursday, September 17, 2009 4:30 PM

Answers

  • Hi Rich,

    I also spent a couple of hours on this interesting question and I have found something. 

    I used such a JoinIntoAccount method and RunQuery method:
    ==============================================================
    public void JoinIntoAccount()

    {

        Table<Account> oAccounts = oDataContext.GetTable<Account>();

        IQueryable<Account> oAccountsQuery = from oAccount in oAccounts

        oQuery = oQuery.Join(oAccountsQuery, oResult => oResult.Company.CompanyID, oAccount => oAccount.CompanyID, (oResult, oAccount) => new Result() { Company = oResult.Company, Account = oAccount });

    }

     

    public List<Result> RunQuery()

    {

        oQuery = oQuery.Distinct();

        oQuery = oQuery.OrderBy(oResult => oResult.Company.CompanyName);

        return oQuery.ToList<Result>();

    }
    ==============================================================
    And these queries:
    ==============================================================
    Search search = new Search();

    search.JoinIntoAccount();

    search.ActiveAccountsOnly();

    search.FilterCompanyName("Company");

    var query = search.RunQuery();
    ==============================================================

    As we all known, LINQ to SQL provider generates the SQL commands by parsing the Expression Tree of the LINQ query.  The oQuery.Expression properties can give us the final Expression (
    Expression Tree Visualizer can help us to debug such problem):
    ==============================================================
    Table(Company).Select(oCompany => new Result() {Company = oCompany}).Join(Table(Account).Select(oAccount => oAccount), oResult => oResult.Company.CompanyID, oAccount => oAccount.CompanyID, (oResult, oAccount) => new Result() {Company = oResult.Company, Account = oAccount}).Where(oResult => (oResult.Account.IsDeleted = False)).Where(oResult => oResult.Company.CompanyName.Contains(value(CSLinqToSQLWinForm.Search+<>c__DisplayClass0).sCompanyNames)).Distinct().OrderBy(oResult => oResult.Company.CompanyName)
    ==============================================================
    Please pay attention to the words in bold.  We can trace these words to get the root Table(Company).   So the provider is able to generate the correct SQL command based on such LINQ query. 

    However, if we use the method I suggested to use a new constructor of the Result class, provider cannot trace the root table if we call Where or OrderBy method at last to retrieve the oResult.Company.CompanyName, since we do not retrieve the Company object in the former query method (JoinIntoAccount).  The provider cannot recognize the new constructor we build and it also cannot translate the corresponding Where and OrderBy command of the oResult.Company.  While if we don’t call the Where and OrderBy method, the result is fine because the necessary data are all retrieved by the SQL command generated and are mapped to the Result object correctly. 


    Changing the order to call the method can solve the problem of the Where method, but the OrderBy’s problem still exists because
    we need to call it behind the Distinct method.
    ==============================================================
    Search search = new Search();

    search.FilterCompanyName("Company");

    search.JoinIntoAccount();

    search.ActiveAccountsOnly();
    ==============================================================


    Based on your scenario, currently I don’t figure out any better workaround than your original idea to put all the necessary data in the Result object.
    (
    new Result() { Company = oResult.Company, Account = oAccount })


    Besides, here are some other dynamic LINQ to SQL libraries for your references:
    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
    http://www.albahari.com/nutshell/predicatebuilder.aspx
    http://tomasp.net/blog/linq-expand.aspx


    Hope my post is helpful for you.


    Have a nice day, Rich!

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by jonesri Wednesday, September 23, 2009 5:13 PM
    Tuesday, September 22, 2009 3:30 PM
    Moderator

All replies

  • Hi Rich,

     

    I think it is an interesting question.  J  If I understand the problem correctly, the result are the INNER JOIN of several tables, right?   If so, please try the following method. 

     

    I defined a new constructor on the Result class to accept another Result object and copy the data.  For detail, please see these codes:

     

    I have three tables named Project, Iteration, and Resource

    ==================================================================
        public class Search

        {

            public IQueryable<Result> Query { get; set; }

            private DataClasses3DataContext DB { get; set; }

     

            public Search()

            {

                DB = new DataClasses3DataContext();

                Query = from p in DB.Projects select new Result() { project = p };

            }

     

            public void JoinIntoIteration()

            {

                IQueryable<Iteration> iterationQuery = from i in DB.Iterations select i;

                Query = Query.Join(iterationQuery, result => result.project.ProjectID, i => i.ProjectID,

                    (result, i) => new Result(result) { iteration = i });

            }

     

            public void JoinIntoResource()

            {

                IQueryable<Resource> resourceQuery = from r in DB.Resources select r;

                Query = Query.Join(resourceQuery, result => result.iteration.IterationID, r => r.IterationID,

                    (result, r) => new Result(result) { resource = r });

            }

        }

        public class Result

        {

            public Project project { get; set; }

            public Iteration iteration { get; set; }

            public Resource resource { get; set; }

     

            public Result()

            { }

     

            public Result(Result r)

            {

                this.project = r.project;

                this.iteration = r.iteration;

                this.resource = r.resource;

            }

        }
    ==================================================================

     

    Please try this approach and tell me whether it can solve this problem.

     

    Have a nice weekend, Rich!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, September 18, 2009 6:29 AM
    Moderator
  • Hi Lingzhi Sun,

    That is exactly the sort of thing I am after, except unfortunately this doesn't work :-(  I have also tried creating a parameter on result for the previous value (see below) without success.  Do you have any other suggestions?

       public class Result

        {

            public Project project { get; set; }

            public Iteration iteration { get; set; }

            public Resource resource { get; set; }

     

            public Result()

            { }

     

            public Result PreviousResult

            {
               set
               {

                    this.project = value.project;

                    this.iteration = value.iteration;

                    this.resource = value.resource;
               }

            }

        }

    Do you have any other suggestions?

    Thanks

    Rich


    Friday, September 18, 2009 7:32 AM
  • Hi Rich,

     

    My method is working fine at my side.  What problem do you encounter?   Could you please provide me some testing data and data structure of the data tables?   Really appreciate it. 

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, September 18, 2009 9:31 AM
    Moderator
  • Hi Lingzhi Sun,

    I have stripped the code right down my side and found that your suggestion does indeed work.  It is one or more of the other operators available in my Search class that seem to be causing my problem.  For example, I have another method on search something like below:

    public void ActiveOnly()
    {
        Query = Query.Where(oResult => oResult.Resource.IsActive == true);
    }

    After this method has been called my Query seems to lose the concept of what Project is on the Result class.  I have tried rewriting the where clause as follows:

    public void ActiveOnly()
    {
        Query = from oResult in Query
                     where oResult.Resource.IsActive == true
                     select new Result(oResult);
    }

    but I still get the same problem.  Other operations also cause the same effect (e.g. Query = Query.Distinct()).

    Any idea what I am doing wrong here?

    Thanks

    Rich
    Friday, September 18, 2009 2:14 PM
  • Hi Rich,

    The ActiveOnly method is working fine at my side, even the original version:
    =====================================================================
    public void ActiveOnly()
    {
        Query = Query.Where(oResult => oResult.Resource.IsActive == true);
    }

    =====================================================================

    Besides, for the Distinct query, we may need to define our own
    IEqualityComparer(T), if we need to customize the comparison progress.  

    Could you clarify what the problem is when you used the Where and Distinct operators?  What is the order to call these dynamic methods?  I tested the codes by this order:
    =====================================================================
                Search search = new Search();

                search.JoinIntoIteration();

                search.JoinIntoResource();

                search.ActiveResource();
    =====================================================================

    Please feel free to tell me if you have any questions.

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Friday, September 18, 2009 3:35 PM
    Moderator
  • Hi Lingzhi Sun,

    Thanks for sticking with me on this.  I have spent a couple of hours stripping down my solution to identify more specifically where my problem is.  I have found two areas where I am having problems, one if the where clause and one is in sorting.  The distinct operator seems to be fine.

    Below is the code I have for a search class now:

    =========================================================================================================
    public class Search
    {
        IQueryable<Result> oQuery;
        SqlConnection oConnection;
        DataContext oDataContext;

        public Search(string sConnectionString)
        {
            oConnection = new SqlConnection(sConnectionString);
            oDataContext = new DataContext(oConnection);
            Table<Company> oCompanies = oDataContext.GetTable<Company>();

            oQuery = from oCompany in oCompanies
                     select new Result() { Company = oCompany };
        }

        public void JoinIntoAccount()
        {
            Table<Account> oAccounts = oDataContext.GetTable<Account>();
            IQueryable<Account> oAccountsQuery = from oAccount in oAccounts
                                                 select oAccount;
            oQuery = oQuery.Join(oAccountsQuery, oResult => oResult.Company.CompanyID, oAccount => oAccount.CompanyID, (oResult, oAccount) => new Result(oResult) { Account = oAccount });
        }

        public void ActiveAccountsOnly()
        {
            oQuery = oQuery.Where(oResult => oResult.Account.IsDeleted == false);
        }

        public void FilterCompanyName(string sCompanyNames)
        {
            oQuery = oQuery.Where(oResult => oResult.Company.Name.Contains(sCompanyNames));
        }

        public List<Result> RunQuery()
        {
            oQuery = oQuery.Distinct();
            oQuery = oQuery.Select(oResult => new Result() { CompanyName = oResult.Company.Name, Email = oResult.Account.Email });
            return oQuery.ToList<Result>();
        }
    }
    ======================================================================================================

    As you identified in your last post, the following code executes without error:

    ===================================
    Search oSearch = new Search(sMyConnection);
    oSearch.JoinIntoAccount();
    oSearch.ActiveAccountsOnly();
    List<Result> oCompanies = oSearch.RunQuery();
    ===================================

    However, the following code does generate an error:

    ===================================
    Search oSearch = new Search(sMyConnection);
    oSearch.JoinIntoAccount();
    oSearch.ActiveAccountsOnly();
    oSearch.FilterCompanyName("aaa");
    List<Result> oCompanies = oSearch.RunQuery();
    ===================================

    It appears that making reference to the original object in a where clause causes an error along the lines of "The member "Result.Company' has no supported translation to SQL.".
    What I don't really understand about this is that when setting the fields to select out Result.Company is referenced without an error being generated.

    The other problem I have found is in the use of an OrderBy clause.  If the RunQuery method is updated as follows:

    public List<Result> RunQuery()
    {
       oQuery = oQuery.Distinct();
       oQuery = oQuery.Select(oResult => new Result() { CompanyName = oResult.Company.Name, Email = oResult.Account.Email });
       oQuery = oQuery.OrderBy(oResult => oResult.CompanyName);
       return oQuery.ToList<Result>();
    }

    then neither of the above scenarios run without error.

    Interestingly, changing the join as below fixes the problem in both cases:
    oQuery = oQuery.Join(oAccountsQuery, oResult => oResult.Company.CompanyID, oAccount => oAccount.CompanyID, (oResult, oAccount) => new Result() { Account = oAccount, Company = oResult.Company });

    Are you able to recreate this problem your side?

    Thanks

    Rich

    Monday, September 21, 2009 2:16 PM
  • Hi Rich,

    I also spent a couple of hours on this interesting question and I have found something. 

    I used such a JoinIntoAccount method and RunQuery method:
    ==============================================================
    public void JoinIntoAccount()

    {

        Table<Account> oAccounts = oDataContext.GetTable<Account>();

        IQueryable<Account> oAccountsQuery = from oAccount in oAccounts

        oQuery = oQuery.Join(oAccountsQuery, oResult => oResult.Company.CompanyID, oAccount => oAccount.CompanyID, (oResult, oAccount) => new Result() { Company = oResult.Company, Account = oAccount });

    }

     

    public List<Result> RunQuery()

    {

        oQuery = oQuery.Distinct();

        oQuery = oQuery.OrderBy(oResult => oResult.Company.CompanyName);

        return oQuery.ToList<Result>();

    }
    ==============================================================
    And these queries:
    ==============================================================
    Search search = new Search();

    search.JoinIntoAccount();

    search.ActiveAccountsOnly();

    search.FilterCompanyName("Company");

    var query = search.RunQuery();
    ==============================================================

    As we all known, LINQ to SQL provider generates the SQL commands by parsing the Expression Tree of the LINQ query.  The oQuery.Expression properties can give us the final Expression (
    Expression Tree Visualizer can help us to debug such problem):
    ==============================================================
    Table(Company).Select(oCompany => new Result() {Company = oCompany}).Join(Table(Account).Select(oAccount => oAccount), oResult => oResult.Company.CompanyID, oAccount => oAccount.CompanyID, (oResult, oAccount) => new Result() {Company = oResult.Company, Account = oAccount}).Where(oResult => (oResult.Account.IsDeleted = False)).Where(oResult => oResult.Company.CompanyName.Contains(value(CSLinqToSQLWinForm.Search+<>c__DisplayClass0).sCompanyNames)).Distinct().OrderBy(oResult => oResult.Company.CompanyName)
    ==============================================================
    Please pay attention to the words in bold.  We can trace these words to get the root Table(Company).   So the provider is able to generate the correct SQL command based on such LINQ query. 

    However, if we use the method I suggested to use a new constructor of the Result class, provider cannot trace the root table if we call Where or OrderBy method at last to retrieve the oResult.Company.CompanyName, since we do not retrieve the Company object in the former query method (JoinIntoAccount).  The provider cannot recognize the new constructor we build and it also cannot translate the corresponding Where and OrderBy command of the oResult.Company.  While if we don’t call the Where and OrderBy method, the result is fine because the necessary data are all retrieved by the SQL command generated and are mapped to the Result object correctly. 


    Changing the order to call the method can solve the problem of the Where method, but the OrderBy’s problem still exists because
    we need to call it behind the Distinct method.
    ==============================================================
    Search search = new Search();

    search.FilterCompanyName("Company");

    search.JoinIntoAccount();

    search.ActiveAccountsOnly();
    ==============================================================


    Based on your scenario, currently I don’t figure out any better workaround than your original idea to put all the necessary data in the Result object.
    (
    new Result() { Company = oResult.Company, Account = oAccount })


    Besides, here are some other dynamic LINQ to SQL libraries for your references:
    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx
    http://www.albahari.com/nutshell/predicatebuilder.aspx
    http://tomasp.net/blog/linq-expand.aspx


    Hope my post is helpful for you.


    Have a nice day, Rich!

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by jonesri Wednesday, September 23, 2009 5:13 PM
    Tuesday, September 22, 2009 3:30 PM
    Moderator
  • Hi Lingzhi Sun,

    Thanks for you help with this.  I guess I'll stick with setting all the values in every join.

    Rich
    Wednesday, September 23, 2009 5:13 PM