How to Export data to Excel using LINQ to Entity?

Question

I have the data coming from Entity Data model table on my ASP.NET page. Now I have to export this data into Excel on button click.

If it is using OLEDB, it is straight forward as it is here: http://csharp.net-informations.com/excel/csharp-excel-oledb-insert.htm

Here is my function to read data from inquiries table:

var model = from i in myEntity.Inquiries
            where i.User_Id == 5
                        orderby i.TX_Id descending
                        select new {
                            RequestID = i.TX_Id,
                            CustomerName = i.CustomerMaster.FirstName,
                            RequestDate = i.RequestDate,
                            Email = i.CustomerMaster.MS_Id,
                            DocDescription = i.Document.Description,
                            ProductName = i.Product.Name

Answer 1

You can still insert into the Excel spreadsheet using the same technique as identified in the linked article.

Just use the following psuedo code

try
 {
    System.Data.OleDb.OleDbConnection MyConnection ;
    System.Data.OleDb.OleDbCommand myCommand = new System.Data.OleDb.OleDbCommand();
    string sql = null;
    MyConnection = new System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\csharp.net-informations.xls';Extended Properties=Excel 8.0;");
    MyConnection.Open();
    myCommand.Connection = MyConnection;

    myCommand.CommandText = "Insert into [Sheet1$] (id,name) values('@p1', '@p2')";
    myCommand.Parameters.Add("@p1", OleDbType.VarChar, 100);
    myCommand.Parameters.Add("@p2", OleDbType.VarChar, 100);

    // define query to entity data model
    var model = from i in myEntity.Inquiries select i;

    foreach(var m in model)
    {    
       cmd.Parameters["@p1"].Value = m.RequestID;
       cmd.Parameters["@p2"].Value = m.CustomerName;
       // .. Add other parameters here
      cmd.ExecuteNonQuery();
    }
  } 

Answer 2

You can just write a string representation of your data - tab delimited for each field and \r\n delimited for each row. Then stream that out the browser as a .csv file, which will open automatically in Excel.

Answer 3

You could use reflection to get a list of properties, then use that list of properties (and reflection) to plug the result of your query into an old ADO.Net DataTable. DataTable has WriteXML, which can be used to store a temporary XML file in the temp folder (using System.IO). Then simply use OpenXML in the Excel Application.

I have code samples at http://social.msdn.microsoft.com/Forums/en-US/whatforum/thread/69869649-a238-4af9-8059-55499b50dd57 if you want to try this approach. IMO this seems to be the quickest (at least, a lot quicker then trying to write directly to Excel), easiest(at least, a lot easier then trying to convert your query to some XML format on your own), and most reusable method (plus in the code sample we're late-binding, so you can develop against mixed environment assuming at least Excel 2003).