In this post I will describe the process you need to go through to get a stored procedure to return a complex type in the Entity Framework.
It is very easy in the Entity Framework to quickly create crud functions that will manipulate data in your tables. However if you want to use stored procedures that return data that is not mapped to an entity from your database, then there are a few more hoops you need to jump through.
Firstly, by examining the stored procedure, you can determine what data types are returned from the stored procedure.
This following method is a quick and dirty one I have used, there may be a better way to do this, and if there is please let me know. There is a tool called Entity Developer from devart which may do the same, but as yet I haven’t had the chance to use it.
- Switch to your *.edmx file and drop a new entity from the toolbox on to the design surface. You may need to delete the Id property that is automatically created if you don’t need it.
- Then right click on any of the entries in the model browser and choose ‘Update Model From Database’
- Choose any tables that your procedure uses from the Add tab. This will map the tables as entities, allowing you to simply copy and paste entity properties from the tables into your new entity.
- Add your stored procedure.
- Right click on ‘Function Imports’ within the Entity Container in the Model Browser and choose ‘Create Function Import’.
- This then gives you the options to Choose the stored procedure, its name and most importantly the Entity that it will be imported to. Choose your new entity you have just created.
Now your new entity can be called
1: ProjectMVCEntities myEntities = new ProjectMVCEntities();
2:
3: var viewData = myEntities.Job(0, 4).ToList();
4:
5: return View();
This is taken from an MVC project, hence the return value of View(), but I hope you get the idea.
Now the stored procedure will be executed with any required parameters and the Job entity will hold the data.
LINQ to XML is a simple toolset that allows developers to easily interact with XML, whether it be in file form of on the wire in string form.
Write to an XML file
There are a few methods within the System.Xml.Linq namespace which make this interaction a breeze. The four main ones are:-
- XDocument – Method which creates the XML document
- XDeclaration – Allows you to set the declaration at the top of the file.
- XElement – Creates a hierarchical element structure
- XAttribute – Creates any attributes related to an element.
The following code shows a very simple way to generate an XML document for members of a club.
1: Member[] members = new[] {
2: new Member{Age = 33, FullName = "John Havers", Title = "Mr"},
3: new Member{Age = 28, FullName = "Robin Southgate", Title = "Mrs"},
4: new Member{Age = 45, FullName = "Paul Chatwell", Title = "Mr"},
5: };
6:
7: XDocument doc = new XDocument(new XDeclaration("1.0", "utf-8", "yes"),
8: new XElement("members",
9: from member in members
10: select new XElement("member",
11: new XAttribute("age", member.Age),
12: new XAttribute("title", member.Title),
13: new XElement("fullname", member.FullName)
14: )
15: )
16: );
17:
18: doc.Save(@"c:\members.xml");
This would generate this XML file.
1: <?xml version="1.0" encoding="utf-8" standalone="yes" ?>
2: <members>
3: <member age="33" title="Mr">
4: <fullname>John Havers</fullname>
5: </member>
6: <member age="28" title="Mrs">
7: <fullname>Robin Southgate</fullname>
8: </member>
9: <member age="45" title="Mr">
10: <fullname>Paul Chatwell</fullname>
11: </member>
12: </members>
Reading from an XML file
Firstly use the Load method of the XDocument class, then a simple LINQ query will return all elements within the root element like this.
1: IEnumerable<XElement> q = from c in loaded.Root.Elements("member")
2: select c;
3:
4: foreach (XElement element in q)
5: {
6: foreach (XElement r in element.Elements())
7: {
8: Console.WriteLine(r.Value);
9: }
10: }
This query lists all the elements called ‘member’ within the root. Then for each member element it will again for each through for any elements within those. In our example the members full name is located there, so a simple value output will write the data to the console.
You can still use var for the query, but during running of the code it is possible to determine the resultant type.
This shows that it is of type XElement and that it is enumerable so you can change var to IENumerable<XElement> to give the code more meaning.