Readdy Write  
0,00 €
Your View Money
Views: Count
Self 20% 0
Your Content 60% 0

Users by Links 0
u1*(Content+Views) 10% 0
Follow-Follower 0
s2*(Income) 5% 0

Count
Followers 0
Login Register as User

Linq: Left join over 3 tables

24.04.2018 (👁4366)


Example of a left join query in Linq with 4 tables

 

This example shows how to create a complex left-join query in Linq in Asp.Net Core 2 MVC

The query consists of Notes, which are bound to the user and user info. In addition, the sum information is connected.

 

The relationship of the tables (scheme)

 

 

Application:

This displays the list in Readdy.Net

[Lnk "https://readdy.net" /]

this is made up of users,

 

 

Linq with left join over the three tables

    //--< Get Linq.Query >--

    //*gets last 10 Notes with View_Sum

    var query = (from n in _dbContext.tbl_Notes

      join u in _dbContext.Users on n.IDUser equals u.IDUser into user_and_notes

 

         from un in user_and_notes.DefaultIfEmpty()

         join s in _dbContext.tbl_User_Sums on n.IDUser equals s.IDUser into user_and_notes_and_sums

 

         from uns in user_and_notes_and_sums.DefaultIfEmpty()

         join i in _dbContext.tbl_User_Infos on n.IDUser equals i.IDUser into user_and_notes_and_sums_and_infos

 

         from q in user_and_notes_and_sums_and_infos.DefaultIfEmpty()

         where n.IsDraft==false

      orderby n.IDNote descending

      select new { n, un.UserName, uns.SumFollowers, uns.intSumViews_Others,q.has_Profil_Image }).Take(30);

    //--</ Get Linq.Query >--

 

 

 

 

 

Controller code, Asp.Net Core2 with Linq and output to the view

// GET: /Note Root

public async Task<IActionResult> Index_All()

{

    ///-------------< Index_All >-------------

    //--< Get User ID >--

    //internal referenz-Number for tracking in tables

    long IDCurrent_User = await UserInfo_Methods.getIDUser_as_Number(this.User,_dbContext ) ;

    //--</ Get User ID >--

 

    //< Own_Views_logger >

    await Counter_Logger.counter_Note_List_erhoehen(IDCurrent_User);

    //</ Own_Views_logger >

 

 

    //--< Get Linq.Query >--

    //*gets last 10 Notes with View_Sum

    var query = (from n in _dbContext.tbl_Notes

      join u in _dbContext.Users on n.IDUser equals u.IDUser into user_and_notes

 

         from un in user_and_notes.DefaultIfEmpty()

         join s in _dbContext.tbl_User_Sums on n.IDUser equals s.IDUser into user_and_notes_and_sums

 

         from uns in user_and_notes_and_sums.DefaultIfEmpty()

         join i in _dbContext.tbl_User_Infos on n.IDUser equals i.IDUser into user_and_notes_and_sums_and_infos

 

         from q in user_and_notes_and_sums_and_infos.DefaultIfEmpty()

         where n.IsDraft==false

      orderby n.IDNote descending

      select new { n, un.UserName, uns.SumFollowers, uns.intSumViews_Others,q.has_Profil_Image }).Take(30);

    //--</ Get Linq.Query >--

 

 

    //----< fill Data_to_View >----

    List<Notes_Index_DataModel> dataList = new List<Notes_Index_DataModel>();

    //---< @Loop: Rows >---

    foreach (var row in query)

    {

        //--< Row to Data >--

        //< correct >

        string sShort = row.n.Text;

        if (sShort.Length > 255) { sShort = sShort.Substring(0, 255); }

       

        row.n.Text = sShort;

        //</ correct >

       

        //< Data >

        Notes_Index_DataModel item = new Notes_Index_DataModel();

        item.Note = row.n;

        item.Ownername = row.UserName;

        item.has_Profil_Image = System.Convert.ToBoolean(row.has_Profil_Image);

        item.sumFollowers = row.SumFollowers;

        item.sumViews = row.intSumViews_Others;

        //</ Data >

       

        //< add >

        dataList.Add(item);

        //</ add >

        //--</ Row to Data >--

    }

    //---</ @Loop: Rows >---

    //----</ fill Data_to_View >----

 

    //< data to view >

    Notes_Index_View_DataModel dataView = new Notes_Index_View_DataModel();

    dataView.IDCurrent_User = IDCurrent_User;

    dataView.List_Notes_with_Owner = dataList;

    //</ data to view >

 

    //< out to view >

    if(Request_Methods.check_Browser_IsMobile(Request)==true)

    {

return View("index_all_mobile", dataView);

    }

    else

    {

return View("index_all", dataView);

    }  

    //</ out to view >

    ///-------------</ Index_All >-------------

}