Beispiel einer Left Join Abfrage in Linq mit 4 Tabellen
Dieses Beispiel zeigt, wie man eine komplexe Left-Join Abfrage in Linq in Asp.Net Core 2 MVC erstellt
Die Abfrage besteht aus Notes, welche an den User und User-Infos gebunden werden. Zudem werden die Summen-Informationen angebunden.
Die Beziehung der Tabellen (Schema)
Anwendung:
Dadurch wird die List in Readdy.Net angezeigt
diese setzt sich zusammen aus User,
Linq mit Left Join รผber die drei Tabellen
//--< 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 mit Linq und Ausgabe auf die 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 >------------- }
|