none
Query with dynamic multiple AND conditions RRS feed

  • Question

  • I work with C#, WPF .NET 4.5.

    I have a database with 3 tables.

    Projects                    ProjectID   int,      Project      varchar(50)

    Keywords                 KeywordID  int,    Keyword      nvarchar(50)

    ProjectsKeywords    ProjectID     int,    KeywordID  int

    Projects and Keywords are joined via ProjectsKeywords.

    From those tables I have created Entity Framework. The created entities are Project and Keyword (Many-to-Many). The Project entity has the Keywords Navigation Property, and the Keyword entity has the Projects Navigation Property.

    I have the following selectedKeywordList collection which contains KeyID values (dynamic quantity of int values).

    public class SelectedKeywords

    {

       public int KeyID { get; set; }

       public string KeywordName { get; set; }

    }

    List<SelectedKeywords> selectedKeywordList = new List<SelectedKeywords>();

    What I want is to extract each project that has all the KeyID's.

    I need a detailed LINQ solution. Any help will be appreciated.

    Thanks


    דוד

    • Moved by Andy ONeill Friday, February 5, 2016 2:22 PM not wpf specific
    • Moved by CoolDadTx Friday, February 5, 2016 3:09 PM EF related
    Friday, February 5, 2016 1:44 PM

Answers

  • Hi Yamnik,

    The relationship between the tables Projects and Keywords is represented as a navigation property, each Projects property in the Keywords entity will only contain the Projects that have this particular Keywords.

    Putting it the other way round - every Projects 's Keywords property only contains the Keywords that particular Project has.

    Given a set of Keywords KeywordIDs to look for you can use this to get the list of Projects that have a Keyword within that set:

    using (var db = new ManyToManyEntities())
    
                {
    
                    List<SelectedKeywords> selectedKeywordList = new List<SelectedKeywords>();
    
                    List<int> keyIds = new List<int>();
    
                    foreach (var sk in selectedKeywordList)
    
                    {
    
                        keyIds.Add(sk.KeyID);
    
                    }
    
                    var project = db.Projects
    
                                     .Where(x => x.Keywords.Any(r => keyIds.Contains(r.KeywordID)));
    
                }
    

    Best regards,

    Cole Wu

    Thursday, February 11, 2016 8:20 AM
    Moderator

All replies

  • This is a c# question rather than wpf.


    Hope that helps.

    Technet articles: WPF: Layout Lab; All my Technet Articles

    Friday, February 5, 2016 2:22 PM
  • Hi Yamnik,

    The relationship between the tables Projects and Keywords is represented as a navigation property, each Projects property in the Keywords entity will only contain the Projects that have this particular Keywords.

    Putting it the other way round - every Projects 's Keywords property only contains the Keywords that particular Project has.

    Given a set of Keywords KeywordIDs to look for you can use this to get the list of Projects that have a Keyword within that set:

    using (var db = new ManyToManyEntities())
    
                {
    
                    List<SelectedKeywords> selectedKeywordList = new List<SelectedKeywords>();
    
                    List<int> keyIds = new List<int>();
    
                    foreach (var sk in selectedKeywordList)
    
                    {
    
                        keyIds.Add(sk.KeyID);
    
                    }
    
                    var project = db.Projects
    
                                     .Where(x => x.Keywords.Any(r => keyIds.Contains(r.KeywordID)));
    
                }
    

    Best regards,

    Cole Wu

    Thursday, February 11, 2016 8:20 AM
    Moderator