Skip to main content

Left outer join in linq

Let's have a scenario in which there are two tables Teachers and Teacher_Courses .Teachers are assigned courses having teacherid in Teacher_Courses table as the foreign key. So to pick up all the Teacher's list with no of courses assigned regardless of having been assigned a course or not in linq you will be using left outer join as follows.


     using (TeacherContext context = new TeacherContext())
     {
         var Teachers = context.Teachers;
         var TeacherCourse = context.TeacherCourses;
         var q = from c in Teachers
                 join o in TeacherCourse on
                 c.int_TeacherId equals o.int_TeacherId into j
                 from Course in j.DefaultIfEmpty().GroupBy(m=>m.int_TeacherId)                        
                 select new
                 {
                      Teachers = c.vcr_TeacherName,
                      Courses =  Course.Count() == 0 ? "(no Courses Assigned)" :  Course.Where(m => m.int_TeacherId == 
c.int_TeacherId).Count().ToString()
                 };

                 foreach (var item in q.ToList())
                    Console.WriteLine(string.Format("Teacher: {0} No of Courses {1}",item.Teachers, item.Courses));  
     }

Comments

Popular posts from this blog

Asp.net mvc razor render partial view using ajax helper

This is the extension to my blog in which I demonstrated rendering of the partial view using jquery Ajax . I want to demonstrate here yet another way by which partial view can be rendered without page refresh. Here is the implementation. Step 1: I will again be using DisplayData class in my demo. Here is it. public class DisplayData { public int ID { get; set; } public DisplayData(int ID) { this.ID = ID; } } Step 2: Create a PartialDemo page @model IEnumerable<MvcApplication5.Models.DisplayData> @{ ViewBag.Title = "PartialDemo"; } @Ajax.ActionLink("Click 1", "PartialDemo", "PartialDemo", new {Data= "1" }, new AjaxOptions { UpdateTargetId = "rsvpmsg" }) @Ajax.ActionLink("Click 2", "PartialDemo", "PartialDemo", new {Data= "2" }, new AjaxOptions { UpdateTargetId = "rsvpmsg" }) <div id="rsvpms...

Asp.net mvc razor render partial view using jquery Ajax

I will going to demonstrate how we can render PartialViews using Jquery Ajax. I will be clicking an a href link ,then I will be calling the controller through jquery Ajax which will fill the partialview for a really nice user experience. Step 1: First of all we will be creating an DisplayData class for the use for this example in the model. public class DisplayData { public int ID { get; set; } public DisplayData(int ID) { this.ID = ID; } } Step 2: We will create a Clicks page and write the following code on it. Specially note empty here which will going to empty and then fill partialview with new records. $(document).ready(function () { $('.msg').click(function () { var id = this.id; $.ajax({ url: "/Category/Display", data: { data: id }, success: function (mydata) { $("#link").empty().appe...

Dotnetnuke Inter-module communication (IMC) simplified on version 06.00.02 with c#

Few days ago I developed a module in which I used IMC which really interested me so I decided to write about it. I will try to explain in this post everything that is necessary to make IMC work in the modules. What is Inter Module Communication? As the name implies if you want to communicate or in other words send data from one module to another IMC is one way of doing it. I will be using module A and module B as the names in my post. An Observation: One thing I observed while playing around with it that if module A is on page 1 and module B is on page 2 then the data doesn't get passed. If both the modules are on the same page then only the data get passed. Example: The basic exercise that I will be performing is to take input from the textbox in Module A and display it in label in Module B. 1) You will be using IModuleCommunicator and IModuleListener interfaces to make this communication works. You will be implementing IModuleCommunicator in the class in the modul...