Create XML from Database

Suppose we have below table in database. Now I want to make xml of all the products which have stock greater than 0. Here I am using LINQ Pad to query data from the database. It is a great tool to query data from database using LINQ to SQL, LINQ to XML, LINQ to Entity Framework.


 CREATE TABLE Product (
 ProductID int IDENTITY(1,1) NOT NULL,
 ProductName varchar(50) NOT NULL,
 Price float NOT NULL,
 Stock int NOT NULL
)
GO
INSERT INTO Product (ProductName,Price,Stock)VALUES('P001',12.12,100)
INSERT INTO Product (ProductName,Price,Stock)VALUES('P002',102.12,200)
INSERT INTO Product (ProductName,Price,Stock)VALUES('P003',104.12,500)
INSERT INTO Product (ProductName,Price,Stock)VALUES('P004',108.12,100)
INSERT INTO Product (ProductName,Price,Stock)VALUES('P005',72.12,10)
INSERT INTO Product (ProductName,Price,Stock)VALUES('P006',72.12,0)
GO
SELECT * FROM Product

Now, query the data from above table using LINQ. We can also save the output to xml file.


 //Export above xml to xmlfile
XElement.Save(Server.MapPath(@"~/export.xml"));

Difference between Var and IEnumerable

Var VS IEnumerable

In previous article I explain the difference between IEnumerable and IQuerable, IEnumerable and IList. In this article I expose the difference between Var and IEnumerable. IEnumerable is an interface that can move forward only over a collection, it can’t move backward and between the items. Var is used to declare implicitly typed local variable means it tells the compiler to figure out the type of the variable at compilation time. A var variable must be initialized at the time of declaration. Both have its own importance to query data and data manipulation.

Var Type

Since Var is anonymous types, hence use it whenever you don't know the type of output or it is anonymous. Suppose you are joining two tables and retrieving data from both the tables then the result will be an Anonymous type.


 var q =(from e in tblEmployee 
 join d in tblDept on e.DeptID equals d.DeptID 
 select new 
 { 
 e.EmpID,
 e.FirstName,
 d.DeptName,
 });

In above query, result is coming from both the tables so use Var type.


 var q =(from e in tblEmployee where e.City=="Delhi" select new { 
 e.EmpID,
 FullName=e.FirstName+" "+e.LastName, 
 e.Salary 
});

In above query, result is coming only from single table but we are combining the employee's FirstName and LastName to new type FullName that is annonymous type so use Var type. Hence use Var type when you want to make a "custom" type on the fly.

More over Var acts like as IQueryable since it execute select query on server side with all filters. Refer below examples for explanation.

IEnumerable Example


 MyDataContext dc = new MyDataContext ();
IEnumerable<Employee> list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10);

Generated SQL statements of above query will be :


 SELECT [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0] WHERE [t0].[EmpName] LIKE @p0

Notice that in this query "top 10" is missing since IEnumerable filters records on client side

Var Example


 MyDataContext dc = new MyDataContext ();
var list = dc.Employees.Where(p => p.Name.StartsWith("S"));
list = list.Take<Employee>(10);

Generated SQL statements of above query will be :


 SELECT TOP 10 [t0].[EmpID], [t0].[EmpName], [t0].[Salary] FROM [Employee] AS [t0] WHERE [t0].[EmpName] LIKE @p0

Notice that in this query "top 10" is exist since var is a IQueryable type that executes query in SQL server with all filters.

IEnumerable Type

IEnumerable is a forward only collection and is useful when we already know the type of query result. In below query the result will be a list of employee that can be mapped (type cast) to employee table.


 IEnumerable<tblEmployee> lst =(from e in tblEmployee 
where e.City=="Delhi" 
select e);

Note

Use Var type when you want to make a "custom" type on the fly.
Use IEnumerable when you already know the type of query result.
Var is also good for remote collection.
IEnumerable is good for in-memory collection.