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

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

  • Comments 10

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!!!

 

Attachment: Program.cs
Leave a Comment
  • Please add 8 and 1 and type the answer here:
  • Post
  • Getting data from SQL, XML and Object and joining them using Language Integrated Query is something you

  • I'm not sure if this is a typo or if it is meant to be this way, but isn't the first join statement supposed to look like this: join dep in GetDepartments() on dep.DeptId equals emp.DeptID ?

  • Yes Bharath,

    You are right. But I copied the code from a running apps. I have no idea how it happened. Anyways I have corrected it. Many thanks for pointing it out.

    Regards,

    Wriju

  • I keep getting "Cannot implicitly convert type 'System.Collections.Generic.List<AnonymousType#1>' to 'System.Collections.Generic.List.....

    error on my web app when I call GetDepartments.

    any idea?

    thanks,

    CK

  • Which version of Visual Studio are you using?

  • Thanks for the prompt response. I figured out the issue though. I was not constructing LINQ query correctly. I was missing "Departments" on "select new Departments".

    I am using VS 2008 indeed.

  • Have you checked the time this whole coding takes. Because I run the same query and it took a lot of time, then I checked into SQL Profiler and it seems that first its getting all Employees from database and then joining them with XML. So if you have 10000 employees it will never open the page fast.

  • @Marc,

    Rightly said, this code is pure in memory operation and has to be implemented with moderation.

    Thanks for the observation.

    -WG

  • What about doing it the other way around? In my case the 'Main' data is an XML tree (column of Table1) for which I want to get details from another table, Table2. I would rather get only the elements that mathch the join as Table2 is rather large.

    Any ideas?

    Thanks,

    MF

  • Thanks for the insight.

    I was quite dissapointed to learn that Linq to Sql and Linq to objects can not be intermixed to perform joins.

    I'm not quite sure on how .NET handles calling ToList() on a database table, but it seems to me it loads the entire table into memory, which seems like a performance killer. I used a similar solution on a small datatable and it works fine, but i fear for using this solution on large tables.

    I wonder if AsEnumerable could help out? (havn't had the time to test it out) Maybe in some cases it might boost performance to select a subset from your database, before performing the actual join...

    Either way, this post was very helpful.

    As a bonus, here is what my final Linq query looks like (notice the alternative way to join the data sets):

    var imagesFromDb = (from i in data.tblImageTexts.ToList()

    from m in imagesToFetch

    where i.Basename == m.BaseName &&

    i.ParentId == m.ID && i.Nr == m.Nr

    select new { i.Text, m.Match });

Page 1 of 1 (10 items)