locked
linq building dynamic tree RRS feed

  • Question

  • Hi,

    I browsed the forum and found no answer to my problem.

    Here it is.

    I, for simplicity, have two tables with a many to many relationship.
    I wish to make dynamic queries at runtime to meet the needs of users.
    For that, I plan using the expression tree builder at run-time.
    But I do not know how to write expression with many to many  relationship.

    Here is the static query.

                    var query = r1
                             . AsNoTracking ()
                             . Include ("category")   // relation many-to-many
                             . Include ("motclef")    // relation many-to-many
                             . Include ("objective") // realtion 1 to ùany
                             . Where (p => p.Categorie.Any (c => c.Libelle.Contains ("SPORT") | | c.Libelle.Contains ("ART")))
                             . Where (p => p.MotClef.Any (c => c.Keyword.Contains ("CHARADE") )
                             . Where (p => p.Objectif.LensIdNumber == 139)
                             . Select (p => p);

    Here's what I would get:
        . dynamic addition of related entities such as:
                     <PhotoExif> Db.PhotoExifs.AsQueryable IQueryable query = ();
                     foreach (string include in includes)
                     {
                         query = query.Include (include);
                     }

      . dynamically adding Where clauses, each clause can be optional

      thank you in advance,

    PS: Maybe using Linq-to-Sql  would be better?

    Thursday, August 22, 2013 9:31 AM

Answers

  • Hello MarcGrenoble,

    Welcome toMSDN Support Forum.

    From your description, you want to know how to write expression with many to many relationship.

    I do not know clearly the table structure of your program and what you want to get as your description with dynamic addition of related entities, so I assume that you want to use LINQ Dynamic Query.

    If it is, here is a blog that shows how to use the LINQ Dynamic Query and have a lot of sample, you can download them and learn them:

    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    And as you mentioned, Linq-to-Sql, is a framework actually. I guess what you think better is LINQ Query Expression. Its syntax is similar with the sql statement written in database.   

    In your case, you can write it like:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    namespace EFAPP
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
                DateTime dt = new DateTime(2013, 08, 14);
                using (DBForWeek3Entities3 db = new DBForWeek3Entities3())
                {
                    var ds = (from f in db.Films
                              join r in db.Releases on f.FilmID equals r.FilmID into f_join_r
                              from fr in f_join_r
                              join rd in db.ReleaseDates on fr.ReleaseId equals rd.ReleaseID into fr_join_rd
                              from frrd in fr_join_rd
                              orderby frrd.Date descending
                              select new { f.FilmID, f.Title, fr.ReleaseId, frrd.Date }).ToList();
                    this.dataGridView1.DataSource = ds;
                }
            }
        }
    }

    You see that it is similar with the statement written in database.

    There is links about information about LINQ Query Expression:

    LINQ Query Expressions:

    http://msdn.microsoft.com/en-us/library/bb397676.aspx

    Query Syntax and Method Syntax in LINQ:

    http://msdn.microsoft.com/en-us/library/vstudio/bb397947.aspx

    I look forward to hearing from you.

    Best Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Saturday, August 24, 2013 6:26 AM
  • Hi,

    Thanks to all for your help.

    The links provided helps me a lot.

    Bests regards

    • Marked as answer by MarcGrenoble Thursday, August 29, 2013 1:22 PM
    Thursday, August 29, 2013 1:20 PM

All replies

  • Hello,

    If it is possible, could you please share your table structure.

    I think your problem will be solved faster with your it. 

    Friday, August 23, 2013 2:22 AM
  • Hello MarcGrenoble,

    Welcome toMSDN Support Forum.

    From your description, you want to know how to write expression with many to many relationship.

    I do not know clearly the table structure of your program and what you want to get as your description with dynamic addition of related entities, so I assume that you want to use LINQ Dynamic Query.

    If it is, here is a blog that shows how to use the LINQ Dynamic Query and have a lot of sample, you can download them and learn them:

    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    And as you mentioned, Linq-to-Sql, is a framework actually. I guess what you think better is LINQ Query Expression. Its syntax is similar with the sql statement written in database.   

    In your case, you can write it like:

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    namespace EFAPP
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
                DateTime dt = new DateTime(2013, 08, 14);
                using (DBForWeek3Entities3 db = new DBForWeek3Entities3())
                {
                    var ds = (from f in db.Films
                              join r in db.Releases on f.FilmID equals r.FilmID into f_join_r
                              from fr in f_join_r
                              join rd in db.ReleaseDates on fr.ReleaseId equals rd.ReleaseID into fr_join_rd
                              from frrd in fr_join_rd
                              orderby frrd.Date descending
                              select new { f.FilmID, f.Title, fr.ReleaseId, frrd.Date }).ToList();
                    this.dataGridView1.DataSource = ds;
                }
            }
        }
    }

    You see that it is similar with the statement written in database.

    There is links about information about LINQ Query Expression:

    LINQ Query Expressions:

    http://msdn.microsoft.com/en-us/library/bb397676.aspx

    Query Syntax and Method Syntax in LINQ:

    http://msdn.microsoft.com/en-us/library/vstudio/bb397947.aspx

    I look forward to hearing from you.

    Best Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Saturday, August 24, 2013 6:26 AM
  • Hi,

    Thanks to all for your help.

    The links provided helps me a lot.

    Bests regards

    • Marked as answer by MarcGrenoble Thursday, August 29, 2013 1:22 PM
    Thursday, August 29, 2013 1:20 PM