Wriju's BLOG

.NET, Cloud and everything

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