none
I need to create query with entity framework that will join three tables, two table's ID are in third table, and select field from table 1 which is between two dates in table2. RRS feed

  • Question

  • Hello,

    I need to create query with entity framework that will join three tables, two table's ID are in third table, and select field from table 1 which is between two dates in table2.

    Can you please show how can I convert this query into Entity framework's query ?

    table1 Field

    table2 Datestart, Dateend 

    table3 id-table1 and id-table2

    Select <Field> From <table1> Left join <table2>,<table3> where (DateTimeNow is between Datestart and Dateend) ON table1.id = table3.table1id, table2.id = table3.table2id

    Thank you.

    Thursday, January 18, 2018 12:44 PM

Answers

  • Hi Tatikyan David,

    If you use left join, please refer to the following code.

    using (var db = new EFDemoContext())
    {
          DateTime dateTime = DateTime.Now;
          var result = from t1 in db.Table1
                       join t2 in db.Table2 on t1.Id equals t2.Table1Id into grp1
                       from g1 in grp1.DefaultIfEmpty()
                       join t3 in db.Table3 on g1.Id equals t3.Table2Id into grp2
                       from g2 in grp2.DefaultIfEmpty()
                       where t1.Datestart <= dateTime && Dateend >= dateTime
                   select new
                       {
                           T1Name = t1.T1Name
                       };
    
    }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 19, 2018 5:47 AM
    Moderator

All replies

  • Hi Tatikyan David,

    If you use left join, please refer to the following code.

    using (var db = new EFDemoContext())
    {
          DateTime dateTime = DateTime.Now;
          var result = from t1 in db.Table1
                       join t2 in db.Table2 on t1.Id equals t2.Table1Id into grp1
                       from g1 in grp1.DefaultIfEmpty()
                       join t3 in db.Table3 on g1.Id equals t3.Table2Id into grp2
                       from g2 in grp2.DefaultIfEmpty()
                       where t1.Datestart <= dateTime && Dateend >= dateTime
                   select new
                       {
                           T1Name = t1.T1Name
                       };
    
    }

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 19, 2018 5:47 AM
    Moderator
  • Thank you very much.
    Friday, January 19, 2018 6:57 PM