Welcome to my blog!

How to mock the DataContext EF & DataContext Linq

August 13, 2018 22:45



Linq good articles

March 2, 2018 10:04



Linq to JavaScript



Linq to SQL





Inner Join, Cross Join and Left Outer Join With LINQ to SQL

October 25, 2017 16:55

The join operations in this article are done using COURSE and STUDENT tables. So now I am going to explain the joins to be done using LINQ to SQL step-by-step.

Step 1: Create COURSE and STUDENT Tables in the database as in the following:
     course_id int IDENTITY(1,1) PRIMARY KEY,
    course_name nvarchar(70) NOT NULL,
    course_desc nvarchar(255) NULL,
    modified_date date NULL,
    student_id int IDENTITY(1,1) PRIMARY KEY,
    student_name nvarchar(70),
    student_city nvarchar(30),
    course_id int NOT NULL

Step 2: Define foreign key constraints on the STUDENT table as in the following:

Step 3: Create Data Layer
Create a dbml file (Operation.dbml in this article) and using the Object Relation Designer create a data context class for the STUDENT and COUSRE tables.

In the above figure we drag and drop both the COURSE and STUDENT tables onto the designer map of the Operation.dbml file and that shows the relationship between the COURSE and STUDENT tables.
Step 4: Inner Join in LINQ to SQL
 OperationDataContext odDataContext = new OperationDataContext();
 var studentInfo = from student in odDataContext.STUDENTs
 join course in odDataContext.COURSEs
 on student.course_id equals       course.course_id
 select new { student.student_name, student.student_city, course.course_name, course.course_desc };

In the above code we join the STUDENT table and the COURSE table using the "join" keyword and using the "on" keyword join the tables by the course_id field of both tables. It returns all rows with a common course_id in both tables.
Step 5: Cross Join in LINQ to SQL
 OperationDataContext odDataContext = new OperationDataContext();
 var studentInfo = from student in odDataContext.STUDENTs
 from course in odDataContext.COURSEs
 select new { student.student_name, student.student_city, course.course_name, course.course_desc };

In the above code we are doing a cross-join on both the STUDENT table and the COURSE table. We get all rows from both tables and the total rows are STUDENT table rows * COURSE table rows.
Step 6: Left Join in LINQ to SQL
 OperationDataContext odDataContext = new OperationDataContext();
 var courseInfo = from course in odDataContext.COURSEs
 join student in odDataContext.STUDENTs
 on course.course_id equals  student.course_id into studentInfo
 from students in studentInfo.DefaultIfEmpty()
 select new 
     STUDENTNAME = (students.student_name == null)? NULL":students.student_name, 
     STUDENTCITY = (students.student_city == null)? "NULL":students.student_city,
     COURSENAME = course.course_name, 
     COUSREDESCRIPTION = course.course_desc 

In above code we are doing a Left Join. Here the left table is COURSE. So it will return all rows from the course table not depends STUDENT table course_id field. If the course_id field value is in the COURSE table but not in the STUDENT table then in the row course_name and course_desc fields will show and student_name and student_city fields will show NULL. Here the output will be the total number of rows in the COURSE table.


October 13, 2017 13:12


foreach (var item in searchFilterItems)
    if (item.Rows == null || item.Rows.Count <= 0) continue;
    foreach (var child in item.Rows)
        if (child == null || !child.IsSelected) continue;
        var newItem = new SearchConditionItem
            AuditLocationId = item.Id,
            Id = child.Id


returnList.AddRange(from item in searchFilterItems
where item.Rows != null && item.Rows.Count > 0
from child in item.Rows
where child != null && child.IsSelected
select new SearchConditionItem
    AuditLocationId = item.Id,
    Id = child.Id


Recent posts