LINQ: Query to SQL, XML and Object and JOIN them

Getting data from SQL, XML and Object and joining them using Language Integrated Query is something you might be looking for. I have created a small application which brings data from SQL Server (table name is Employee), XML (file name is Department.xml) and Object (Generic list containing list of Managers).

 

My SQL Table Looks like

 

EmpId int

EmpName varchar(50)

DeptId int

ManagerId int

 

Now the DeptID is linked to an XML file (where the department name is assigned against DeptId is that file)

 

<?xml version="1.0" encoding="utf-8" ?>

<Departments>

  <Department id="1" name="Software"></Department>

  <Department id="2" name="Service"></Department>

  <Department id="3" name="Support"></Department>

</Departments>

 

And to get the XML data from the file located at C:\XMLData you need

 

public static List<Departments> GetDepartments()

{

    //Loading the XML file and storing it in List of type Departments

var query = from e in XElement.Load(@"C:\XMLData\Department.xml").Elements("Department")

                select new Departments

               {

                   DeptID = (int)e.Attribute("id"),

                   DeptName = (string)e.Attribute("name")

               };

    return query.ToList();

}

 

 

 

Manager id is also like that and it has details in Generic List.

 

List<Managers> mgrs = new List<Managers>

{

    new Managers{ManagerId = 1, ManagerName = "Manager 1"},

    new Managers{ManagerId = 2, ManagerName = "Manager 2"}

};

 

Now for each of the three entities I have object representation in my code,

 

/*

Class for the XML data coming from physical file

Using the Automatic feature

*/

public class Departments

{

    public int DeptID{get;set;}

    public string DeptName{get;set;}

}

/*

Class for the Linq to Sql

Table is in SQL Express 2005 Database "Northwind"

Attributes are coming from the namespace "System.Data.Linq.Mapping"

*/

[Table(Name="Employee")]

public class Employees

{

    [Column]

    public int EmpId { get; set; }

  [Column]

    public string EmpName { get; set; }

    [Column]

    public int DeptId { get; set; }

    [Column]

    public int ManagerId { get; set; }

}

/*

This class is for the pure C# List<Managers>

*/

public class Managers

{

    public int ManagerId { get; set; }

    public string ManagerName { get; set; }

}

 

Now you have to join them to get the linked data. Linq comes into the picture to help you out in this scenario.

static void Main(string[] args)

{

    //Connection string for the Linq to Sql

    string sConn =

       @"Data Source=.\SQLEXPRESS;Initial Catalog=Northwind;

                                             Integrated Security=True";

    //Initializing the connection

    DataContext db = new DataContext(sConn);

   

    var query =

        from emp in db.GetTable<Employees>().ToList()

        join dep in GetDepartments() on emp.DeptId equals dep.DeptID

        join mgr in GetManagers() on emp.ManagerId equals mgr.ManagerId

                select new

                       {

                           EmpName = emp.EmpName,

                           DeptName = dep.DeptName,

                           ManagerName = mgr.ManagerName

                       };

    foreach (var res in query)

    {

        Console.WriteLine("Name={0}, Dept={1}, Manager={2}"

                           ,res.EmpName, res.DeptName, res.ManagerName);

    }

}

 

I have attached full source code as attached.

 

Namoskar!!!

 

Program.cs