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,

1 2 3 4 5 6 7

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)

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

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.

Read CDATA section based on where clause using LINQ

Consider an XML as below;
<Book>

  <BookItem ISBN="SKS84747"><![CDATA[20]]> </BookItem>
  <BookItem ISBN="LHKGOI84747"><![CDATA[50]]> </BookItem>
  <BookItem ISBN="PUOT84747"><![CDATA[20]]> </BookItem>
</Book>

This code gives me all the CDATA sections,

var value = from v in x.Descendants("BookItem").OfType<XCData>()
                                select (string)v.Value;

In order to get CDATA of any particular node based on where clause we need to execute below query as,

var value = x.DescendantNodes().OfType<XCData>()
                .Where(m => m.Parent.Name == "BookItem" && m.Parent.Attribute("ISBN").Value == "PUOT84747")
                .ToList();

This statement will return all the CDATA sections matching in where clause.
To get the single item just replace .ToList() with .Select(cdata => cdata.Value.ToString());

var value = x.DescendantNodes().OfType<XCData>()
                .Where(m => m.Parent.Name == "BookItem" && m.Parent.Attribute("ISBN").Value == "PUOT84747")
                .Select(cdata => cdata.Value.ToString());

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.

LINQ update on table having no primary key

I was trying to update a record in a table last night, using LINQ. Everything was fine and running without any errors.
Surpisingly the record was not getting updated. I then attached the modified object to the context. It started giving an exception as,

Can't perform Create, Update or Delete operations on Table because it has no primary key.

Then I realised that there is no primary key assigned to the table. The possible solution was to change the database schema and add primary key 

to that table. But this change was not acceptable.

Further debugging into the code I got the workaround for this. 

When we generate a class file using a tool such as sqlmetal or anything else, certain attributes are associated with the columns of that table.

We can open that class file and manually add IsPrimary= true attribute to any one of the columns.

This will fool the LINQ engine and treat the respective column as a primary key.

Here is an example for the same.

I have generated a class with table as TableWithNoPK. It has 3 columns as Field1, Field2, Field3.

I assigned IsPrimary attribute to field1 as,

[global::System.Data.Linq.Mapping.ColumnAttribute(Name="field1", Storage="_Field1", DbType="VarChar(50)", IsPrimaryKey= true)]
        public string Field1
        {
            get
            {
                return this._Field1;
            }
            set
            {
                if ((this._Field1 != value))
                {
                    this._Field1 = value;
                }
            }
        }

The c# code goes normal as,

var qry = from NoPK in instance.TableWithNoPK
                      select NoPK;

       TableWithNoPK test = qry.FirstOrDefault();
       test.Field3 = "Gud 1";
       instance.SubmitChanges();

Pure Join Table and Entity Framework

A table that contains only foreign keys (sometimes called a pure join table) and represents a many-to-many relationship between two tables in the database will not have a corresponding entity in the conceptual model. When the Entity Data Model tools encounter such a table, the table is represented in the conceptual model as a many-to-many association instead of an entity. 

Performing Inner Joins in LINQ

To start with I have created a database schema with 2 tables as;

1. LeftTable

    2. RightTable

Added the sample data as;

If inner join is applied on these 2 tables the result set will return rows when there is at least one match in both the tables.

Using SQL syntax for inner join we get following results;

SELECT lefttable.lefttablefield1, lefttable.lefttablefield2, lefttable.lefttablefield3

FROM lefttable

INNER JOIN righttable

ON lefttable.lefttablefield1 = righttable.lefttablereferrence

I applied the join syntax which we are using in Dynamic Budgets in most of the cases and got the same result set;

var qry = from leftTable in instance.LeftTable

join rightTable in instance.RightTable on

new { a = leftTable.LeftTableField1 } equals

new { a = rightTable.LeftTablereferrence }

select leftTable;

Found result as;

Nested insert using LINQ

Consider database structure we have created in previous post. Both tables share a relationship.
Now if one wants to insert records in both the tables, 2 seperate queries are required to update each table.
LINQ provides a mechanism where tables are interdependent it manages the insertion of records. i.e. using LINQ one need to fire the insert query only once and that on parent table.

Database structure is;

Here is a code to illustrate that;

string employeename = txtEmployeeName.Text;
string deptName = txtDeptName.Text;

Employee emp = new Employee();
emp.EmployeeName = employeename;

Department dept = new Department();
dept.DeptName = deptName;
dept.Employee.Add(emp);           

instance.Department.InsertOnSubmit(dept);
instance.SubmitChanges();

This will insert records in both the tables at one insert request and it will 

handle the responsibility of updating references.

How to use SQL metal to generate entity set

Let's start with database creation. Here I have created 2 tables as
Employee

       EmployeeID *

       EmployeeName

       DeptID (FK)

Department
       DeptID *
       DepartmentName


Create a windows forms application using Visual studio ( I am using Visual Studio 2010). Let's say LINQSample.
Create a class to have entity set which we are going to generate through SQLMETAL command. I have created a class say LINQSample.cs.
Then create a sqlmetal command as;

cd "E:\MySamples\LINQSample\LINQSample"
sqlmetal /conn:"Integrated Security=SSPI;Persist Security Info=False;INITIAL CATALOG=MySampleDB;DATA SOURCE=(local)" 

/code:E:\MySamples\LINQSample\LINQSample\LINQSample.cs /sprocs /views /namespace:LINQSample

Here I have created project in above mentioned location with database name as MySampleDB. You can use any other names here.

Go to visual studio command prompt and run the above command. This will generate a class with database name. It consist of the entity set provided in database. It is a C# representation of the database where tables are mapped as individual classes which we can use in our code anywhere and perform database operations using c# code.

Perform LINQ join on nullable and non-nullable types

When working with Nullable types, join statement in LINQ need to be handled carefully. When you perform a join over nullable and non-nullable types,
compile time exception is thrown saying that "Type inference failed in the call to join". For example consider following classes.

public class Employee
{
   public int EmployeeId
   {
      get;
      set;
   }
        
   public string EmployeeName
   {
      get;
      set;
   }
}

public class Employee
{
   public int EmployeeId
   {
      get;
      set;
   }
        
   public string EmployeeName
   {
      get;
      set;
   }
}

To fetch data from these let's write code as;

class Test
   {
       static void Main()
       {
           var employees = new List<Employee>
           {
               new Employee
               {
                   EmployeeId = 1,
                   EmployeeName = "E1",
               },
               new Employee
               {
                   EmployeeId = 2,
                   EmployeeName = "E2",
               },
               new Employee
               {
                   EmployeeId = 3,
                   EmployeeName = "E3",
               },
           };

           var departments = new List<Department>
           {
               new Department
               {
                   EmployeeId = 2,
                   EmployeeName = "E2",
               },
               new Department
               {
                   EmployeeId = null,
                   EmployeeName = "D1",
               },
               new Department
               {
                   EmployeeId = 3,
                   EmployeeName = "E3",
               },
           };

           var r =
               from dept in departments
               where dept.EmployeeId != null
               join emp in employees
               on new { SourceEmployeeID = dept.EmployeeId.Value, SourceEmployeeName = dept.EmployeeName }
               equals new { SourceEmployeeID = emp.EmployeeId, SourceEmployeeName = emp.EmployeeName }
               select new
               {
                   emp,
                   dept,
               };

           foreach (var item in r)
           {
               Console.WriteLine("{0}", item.emp.EmployeeId);
           }
       }
   }
}

The query highlighted in above code will match the LHS with EmployeeID which is nullable with RHS EmployeeID which is not nullable.