Exporting a View or Stored Procedure to Excel with Entity Framework

June 26, 2011

The other day I wanted to run a Stored Procedure in Entity Framework, and dump the results into Excel.

Since Entity Framework works with Entity Objects, I needed some code that would take a list of classes like this

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime DOB { get; set; }
}

and turn it into this

Id Name DOB
1 Rachel Lim 6/30/1985
2 Joe Smith 3/30/1958
3 Sarah Spencer 2/21/1964

I didn’t see anything existing which would help me with this, so came up with my own helper class. It takes a list of objects, and writes them to an excel file. Each object is on a separate row, and the object properties are in the columns.

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;

namespace ExportObjectsToExcel
{
    public class ExcelHelpers
    {
        /// <summary>
        /// Exports a list of objects to Excel. Objects go in the Rows while Object Properties go in the Columns
        /// </summary>
        /// <param name="objects">List of objects to export.</param>
        /// <param name="filePath">Location to save file to. Does not need to exist</param>
        /// <param name="fileName">Name of excel file</param>
        public static void ExportToExcel<T>(IEnumerable<T> objects, string filePath, string fileName)
        {
            // Add \ to end of file name if it doesn't exist. Just want to be consistant
            if (!filePath.EndsWith(@"\"))
                filePath += @"\";

            // Create directory if it doesn't exist
            if (!Directory.Exists(filePath))
                Directory.CreateDirectory(filePath);

            // Start Excel and get Application object. 
            Excel.Application excel = new Excel.Application();

            // Set it hidden and hide alerts
            excel.Visible = false;
            excel.DisplayAlerts = false;

            // Create a new workbook. 
            Excel.Workbook workbook = excel.Workbooks.Add();

            // Get the active sheet 
            Excel.Worksheet sheet = (Excel.Worksheet)workbook.ActiveSheet;

            try
            {
                // Convert the list into a rectangular array that Excel can read
                var data = GetObjectArray<T>(objects);

                // If at least one record got converted successfully
                if (data.Length > 1)
                {
                    // Get the range of cells that the data will go into. Size matches rectangular array size
                    string xlsRange = string.Format("A1:{0}{1}",
                        new object[] { GetExcelColumn(data.GetLength(1)), data.GetLength(0) });

                    // Insert data into the specified range of cells
                    Excel.Range range = sheet.get_Range(xlsRange);
                    range.Value = data;

                    // Auto-Fit the columns
                    range.EntireColumn.AutoFit();
                }

                // Save workbook
                workbook.SaveAs(
                    string.Format("{0}{1}", new object[] {filePath, fileName}), 
                    Excel.XlFileFormat.xlWorkbookNormal);
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                // Close
                sheet = null;
                workbook.Close();
                workbook = null;
                excel.Quit();
            }

            // Clean up 
            // NOTE: When in release mode, this does the trick 
            GC.WaitForPendingFinalizers();
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }

        /// <summary>
        /// Takes a List of objects objects and converts the objects and their properties into a rectangular array of objects
        /// </summary>
        /// <param name="objects">List of objects to flatten</param>
        /// <returns>Rectangular array where objects are stored in [0] and properties are stored in [1]</returns>
        private static object[,] GetObjectArray<T>(IEnumerable<T> objects)
        {
            // Get list of object properties
            PropertyInfo[] properties = typeof(T).GetProperties();

            // Create rectangular array based on # of objects and # of object properties
            object[,] data = new object[objects.Count() + 1, properties.Length];

            // Loop through properties on object
            for (int j = 0; j < properties.Count(); j++)
            {
                // Write the property name into the first row of the array
                data[0, j] = properties[j].Name.Replace("_", " ");

                // Loop through objects and write out the specified property of each one into the array
                for (int i = 0; i < objects.Count(); i++)
                {
                    data[i + 1, j] = properties[j].GetValue(objects.ElementAt(i), null);
                }
            }

            // Return rectangular array
            return data;
        }

        /// <summary>
        /// Takes an Integer and converts it into Excel's column header code.
        /// For example: 1 = A; 2 = B; 27 = AA;
        /// </summary>
        /// <param name="colNumber">Number of Column in Excel. 1 = A</param>
        /// <returns>string that Excel can use</returns>
        private static string GetExcelColumn(int colNumber)
        {
            // If value is zero or less, return an empty string
            if (colNumber <= 0)
                return string.Empty;

            // If the value is less than or equal to 26 (Z), the column header
            // is only one character long. If it's greater, call this recursively
            // to get the first letter(s) of the column code.
            string first = (colNumber <= 26 ? string.Empty :
                GetExcelColumn((int)Math.Floor((colNumber - 1) / 26.00)));

            // Get the final letter in the column code
            int second = colNumber % 26;
            if (second == 0) second = 26;
            char finalLetter = (char)('A' + second - 1);            // Excel column header is the first part + the final character
            return string.Format("{0}{1}", new object[] { first, finalLetter });
        }
    }
}

Now all I have to do to export a list of entities is call this helper method and pass it my list of entities, along with the file name and path of the excel file to save it to.

List<MyEntity> list = GetEntityList();
ExcelHelpers.ExportToExcel<MyEntity>(list, filePath, fileName);

Notes

  • You’ll need to add a COM reference to the Microsoft Excel Object Library
  • The code was tested with 30,000 records of 53 columns each, and it ran in a couple of seconds
  • Performance for doing a single excel write is much better than looping through data and writting cell-by-cell. See this code project article for more details
  • If you don’t have .Net Framework 4.0 or greater, you’ll need to add Missing.Value parameters to the excel methods. See this article for an example.
  • If you encounter an error or stop debugging after excel has started but before it ends, you may need to go into Task Manager and close EXCEL.EXE manually

Tricking EF to span Multiple Databases

May 22, 2011

I was working on a project recently that used Entity Framework and had to span multiple databases. I was quite surprised to find out that EF didn’t support making one edmx file span multiple databases, and so came up with this solution to get around that using SQL Synonyms.

Here’s a quick walkthrough of how it works. Lets say I have two databases, located on separate servers. We’ll call them ServerA.DatabaseA and ServerB.DatabaseB. I would like to have one edmx file that contains objects from both databases, and include links between the two.

Setting up the Synonyms

First, I needed to setup my database synonyms. A simple script like this was run on DatabaseB to print out my CREATE SYNONYM statements, and the resulting output got verified and ran on DatabaseA.

declare @name varchar(max)

DECLARE db_cursor CURSOR FOR
select name from sysobjects where type = 'U'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0
BEGIN   

print 'CREATE SYNONYM [dbo].[' + @name + '] FOR [ServerB].[DatabaseB].[dbo].[' + @name + ']
 '
       FETCH NEXT FROM db_cursor INTO @name
END   

CLOSE db_cursor
DEALLOCATE db_cursor

This allowed me to reference tables on ServerB.DatabaseB from ServerA.DatabaseA using their regular table names. For example, if DatabaseA had Customers and DatabaseB had products, this allows me to run

SELECT * FROM Products

from DatabaseA instead of writing

SELECT * FROM ServerB.DatabaseB.dbo.Products

It will also work on Views, Stored Procedures, and User-Defined Functions. The only catch is no two object names can be the same.

Creating the EDMX files

Next, we need to create our edmx files. You will end up with one working copy of the edmx file, and a separate copy for each database you are trying to span.

Create a project for each database containing nothing more than a single edmx file pointing to one of the databases. Name the edmx files the same and make sure the entity namespaces are the same.

Now create a 3rd project and edmx file for your working copy. Make sure it’s also named the same and has the same entity namespace. Point it to whatever database contains the synonyms. In this case, that’s DatabaseA.

Merging the edmx files

Now we need to merge the edmx files. Since they are just xml files, this could be done by hand, but if we did that we’d need to do it every time the database got updated, so a script is far better.

Here is the one I use. Its not perfect, but for an XML dummy like me it worked just fine. I created a new project to hold it, and whenever I made changes to my edmx files I would run this project.

static void Main(string[] args)
{
    // Directory that can access all 3 edmx files
    // Fairly sure there's a better way to do this, but this
    // was the first that came to my mind
    string rootDir = Directory.GetCurrentDirectory() + @"..\..\..\..\";

    // Location of working edmx file to write merged edmx to
    string resultFile = rootDir + @"DAL\Entities.edmx";

    // List of edmx files to merge
    string[] files = new string[]
    {
        rootDir + @"DatabaseA\Entities.edmx",
        rootDir + @"DatabaseB\Entities.edmx"
    };

    // Load result file
    var a = new XmlDocument();
    a.Load(resultFile);

    // TODO: Clear result file of anything except LNK_ items?
    // Actually that might cause a problem with nav properties.
    // Will probably have to create a merged file of list of files,
    // then merge changes from that single file with the result file

    // Loop through files to merge and add their nodes to the result file
    foreach (var edmxFile in files)
    {
        var b = new XmlDocument();
        b.Load(edmxFile);

        string rootNode = "/edmx:Edmx/edmx:Runtime/";

        XmlNamespaceManager nsMan = new XmlNamespaceManager(a.NameTable);
        nsMan.AddNamespace("edmx", "http://schemas.microsoft.com/ado/2008/10/edmx");

        // SSDL
        MergeNodes(
            a.SelectSingleNode(rootNode + "edmx:StorageModels", nsMan)["Schema"]["EntityContainer"],
            b.SelectSingleNode(rootNode + "edmx:StorageModels", nsMan)["Schema"]["EntityContainer"].ChildNodes,
            a, b);

        MergeNodes(
            a.SelectSingleNode(rootNode + "edmx:StorageModels", nsMan)["Schema"],
            b.SelectSingleNode(rootNode + "edmx:StorageModels", nsMan)["Schema"].ChildNodes,
            a, b);

        // CSDL
        MergeNodes(
            a.SelectSingleNode(rootNode + "edmx:ConceptualModels", nsMan)["Schema"]["EntityContainer"],
            b.SelectSingleNode(rootNode + "edmx:ConceptualModels", nsMan)["Schema"]["EntityContainer"].ChildNodes,
            a, b);

        MergeNodes(
            a.SelectSingleNode(rootNode + "edmx:ConceptualModels", nsMan)["Schema"],
            b.SelectSingleNode(rootNode + "edmx:ConceptualModels", nsMan)["Schema"].ChildNodes,
            a, b);

        // MSL
        MergeNodes(
            a.SelectSingleNode(rootNode + "edmx:Mappings", nsMan)["Mapping"]["EntityContainerMapping"],
            b.SelectSingleNode(rootNode + "edmx:Mappings", nsMan)["Mapping"]["EntityContainerMapping"].ChildNodes,
            a, b);

    }

    // Save result file
    a.Save(resultFile);

    Console.WriteLine("Done");
    Console.ReadLine();
}

private static void MergeNodes(XmlNode parentNodeA, XmlNodeList childNodesB, XmlDocument parentA, XmlDocument parentB)
{
    foreach (XmlNode oNode in childNodesB)
    {
        // Exclude container node and comments
        if (oNode.Name == "EntityContainer" || oNode.Name == "#comment") continue;

        bool isFound = false;
        string name = oNode.Attributes["Name"].Value;

        foreach (XmlNode child in parentNodeA.ChildNodes)
        {
            if (child.Name == "EntityContainer" || child.Name == "#comment") continue;

            // If node already exists, exit loop
            if (child.OuterXml == oNode.OuterXml && child.InnerXml == oNode.InnerXml)
            {
                isFound = true;
                Console.WriteLine("Found::NoChanges::" + oNode.Name + "::" + name);
                break;
            }

            // If node by the same name exists
            if (child.Attributes["Name"].Value == name)
            {
                if (oNode.Name == "EntityType")
                {
                    foreach (XmlNode property in child.ChildNodes)
                    {
                        // Make sure to keep any navigation properties that have been added
                        if (property.Name == "NavigationProperty"
                            && property.Attributes["Relationship"] != null
                            && Regex.IsMatch(property.Attributes["Relationship"].Value, @".*\.LNK_.*"))
                        {
                            oNode.AppendChild(parentB.ImportNode(property, true));
                        }
                    }
                }

                isFound = true;
                Console.WriteLine("Found::Replaced::" + oNode.Name + "::" + name);
                parentNodeA.ReplaceChild(parentA.ImportNode(oNode, true), child);
            }
        }        if (!isFound)
        {
            Console.WriteLine("NotFound::Adding::" + oNode.Name + "::" + name);
            parentNodeA.AppendChild(parentA.ImportNode(oNode, true));
        }
    }}

This is a work in progress, but it works for now. I’ll update this post after I fix up the merge project.

Creating Links between the Databases

Now you’ll have one edmx file, containing the information from both databases. You can create Associations between the two objects, however they will be erased anytime you merge your changes. To get around this, make sure all your links contain the same prefix and modify the merge code to exclude NavigationProperties that are prefixed with whatever you use. In my case, I prefixed them with LNK_

Summary

And there you have it. Test it out and you’ll find it works just fine. Your working edmx points to DatabaseA, which contains synonyms for DatabaseB objects. EF has no idea that half its objects exist on another database, because from its point of view it is all one database. Navigational properties between the two databases work just fine providing you’ve manually setup the links in the working copy.

To update the working copy, update the projects containing the individual database edmx, and run the merge code again. Providing you don’t make any changes to the working copy except links, and all your links contain the same prefix to avoid getting erased when the merge occurs, you should have no problems.

Screenshot of MergeEdmx Projects

Notes

  • This only works in a databases that supports Synonyms. I was using SQL Server 2005.
  • This was the first project I did using EF and I was using EF 4. I have no idea if this works on older versions.
  • You have to be sure that items are uniquely named between databases.
  • The Merge code is not perfect – sorry. I plan on fixing it up to remove Deleted items but haven’t gotten around to it. I just have a TODO bit in my code for now. If someone knows of a better tool to merge XML files, let me know! It has to be something an xml-idiot can use though 🙂