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!!!
Comments
Anonymous
July 02, 2007
Getting data from SQL, XML and Object and joining them using Language Integrated Query is something youAnonymous
July 02, 2007
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 ?Anonymous
July 02, 2007
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, WrijuAnonymous
January 24, 2008
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, CKAnonymous
January 24, 2008
Which version of Visual Studio are you using?Anonymous
January 24, 2008
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.Anonymous
December 13, 2008
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.Anonymous
December 17, 2008
@Marc, Rightly said, this code is pure in memory operation and has to be implemented with moderation. Thanks for the observation. -WGAnonymous
March 04, 2009
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, MFAnonymous
May 28, 2009
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 });