What I learnt today: LINQ to SQL

Showing posts with label LINQ to SQL. Show all posts

Left outer join using LINQ - yields different outputs

Couple of days ago, I was working on a project where I was supposed to convert SQL queries in LINQ. There was a simple SQL query as;

SELECT  * FROM Table1 td1LEFT
OUTER JOIN Table2ON 
td1.ColumnName = td2.ColumnName
WHERE td2.ColumnName IS NULL
ORDER BY SomeColumns

I ran this query in SQL Query analyzer, it returned me 100 records. And my converted LINQ code returned me 105 records. I converted this query intto LINQ to perform Left Outer Join as;
I did this in 2 ways;
Method 1:

var data= (from td1in Table1
           join td2 in Table2.Where(a => a.ColumnName == (int?)null) 
           on td1.ColumnName equals td2.ColumnName into outer
           from x in outer.DefaultIfEmpty()
           orderby SomeColumns
           select td1);

Method 2:

var data = from td1 in Table1
           join td2 in Table2
           on td1.ColumnName equals td2.ColumnName into outer
           from item in outer.DefaultIfEmpty()
           where item.ColumnName.Value == (int?)null
           orderby somecolumns
           select td1 ;

This gave me an exception as, failed to enumerate results

var data = from td1 in Table1
           join td2 in Table2
           on td1.ColumnName equals td2.ColumnName into outer
           from item in outer.DefaultIfEmpty()
           where item == null
           orderby somecolumns
           select td1 ;

In my original query that line item.ColumnName.Value == (int?)null was wrong, because I tried to retrieve value for all ColumnName even if item was null. I corrected that with the following query and it worked fine.

Performing self join uisng LINQ

Self join is used when one wants to refer data from the same table. Consider a scenario where we have an Employee table. Each record consist of ManagerID which is again an Employee. Now if we want to get the record of employee-manager relationship.

Consider a class Employee as,

public class Employee
    {
        public int EmpID { get; set; }
        public string EmpName { get; set; }
        public string City { get; set; }
        public int ManagerID { get; set; }
    }

We will create a list of Employee( I am using class, you can fetch records from database table)

List<Employee> emp = new List<Employee>();
emp.Add(new Employee()
{
    EmpID = 1,
    EmpName = "a",
    City = "Pune",
    ManagerID = 11
});

emp.Add(new Employee()
{
    EmpID = 2,
    EmpName = "b",
    City = "mumbai",
    ManagerID = 12
});

emp.Add(new Employee()
{
    EmpID = 3,
    EmpName = "c",
    City = "Pune",
    ManagerID = 2
});

emp.Add(new Employee()
{
    EmpID = 4,
    EmpName = "d",
    City = "Delhi",
    ManagerID = 14
});

Now to query data, self join is written as,

1
2
3

var q = (from employee in emp
        join employee2 in emp on employee.EmpID equals employee2.ManagerID
        select employee).FirstOrDefault();

Using this query you can select child employee as well as parent employee i.e. employee as well as Manager.

DBML designer over SQLMetal

Today I got an assignment from client that the update query is running too slow. I started debugging it and found that the update is using all the fields. I came to know that LINQ to SQL by default assume that all columns participate in opt concurrency and generates a full WHERE clause.
So the query looks like,

UPDATE [dbo].[TableName]
SET [Column1] = @p1, [Column2] = @p2, [Column2] = @p3, [Column3] = @p4, [Column4] = @p5
WHERE [PRIMARYKEY] = @p0 AND
[Column2] = @p2 AND
[Column2] = @p3 AND
[Column3] = @p4 AND
[Column4] = @p5

From this link http://stackoverflow.com/questions/10136450/submitchanges-internally-adds-all-the-fields-as-where-clause-how-to-get-rid-of I came to know about a property UpdateCheck. This has to be set to NEVER in order to remove it from concurrency controlling. But this is not possible with SQLMetal tool because it generates the class structure straight away through command line.

Then I tried DBML designer. In DBML designer you can set the properties for every column from visual studio. So I dropped my table into the designer and it's respective code generated. Both the tools generate the same code, but designer facilitates you with properties window to change them at any point of time.
With UpdateCheck set to never the query gets generated like this,

UPDATE [dbo].[TableName]
SET [Column1] = @p1, [Column2] = @p2, [Column2] = @p3, [Column3] = @p4, [Column4] = @p5
WHERE [PRIMARYKEY] = @p0

To mention the disadvatages of setting UpdateCheck to never is the application looses concurrency control. The where clause gets cut down and allows multiple users to update the table at the same time.

What I learnt today

Left outer join using LINQ - yields different outputs

Performing self join uisng LINQ

DBML designer over SQLMetal

Labels

Pages