C# .NET
Framework 4.0, SQL Server 2008, ADO.NET Entity Data Model, LINQ to Entities
I’ve a GetCompanies SQL Server 2008 stored procedure that returns a resultset like this
|
Field1
|
Field2
|
Field3
|
Field4
|
Field5
|
Field6
|
Field7
|
Field8
|
|
VE
|
7471
|
033333333
|
Environment s.r.l.
|
1
|
Roma, 6
|
Vigonza
|
35010
|
|
VE
|
7499
|
033333322
|
Active s.r.l.
|
4
|
Venezia, 8
|
Padova
|
35100
|
So, in my
application I want to read this resultset data using LINQ to Entities.
1. Add a new ADO.NET Entity Data Model (Shapshot.edmx)
at the solution.
2. Follow the wizard for creating a new
connection to your database and select the GetCompanies (for this example)
stored procedure from the objects list.
3. Click on Model Browser.
4. Select the SnapshotModel tree,
ComplexTypes.
a. Right click on CopmplexTypes and
then click on “Add new Complex Type”.
b. Type a name, for example “CompanyEntity”.
i.
Select
CompanyEntity, right click on “Add” -> “Scalar Property” and select the type
property: Int32, String, Boolean and so on.
ii.
In
my example I obtain a structure like this
1. Field1 -> String
2. Field2 -> Int32
3. Field3 -> String
4. Field4 -> String
5. Field5 -> Int32
6. Field6 -> String
7. Field7 -> String
8. Field8 -> String
c. Expand the Function Imports folder
and double click on GetCompanies stored procedure, you‘ll see a dialog box like
this. Select the “Complex” option button and then the type that you have
created before.
5. Now, you can retrieve data using
this C# code (Company is an internal object).
GetCompanies() returns a System.Data.Objects.ObjectResult<CompanyEntity>
object.
public Company[] GetCompanies()
{
var companiesList = new List<Company>();
using (var context = new SnapshotEntities())
{
var query = context.GetCompanies();
foreach (var c in query)
{
var company = new Company
{
Field1 = c.Field1,
Field2 = c.Field2,
Field3 = c.Field3,
Field4 = c.Field4,
Field5 = c.Field5,
Field6 = c.Field6,
Field7 = c.Field7
Field8 = c.Field8
};
companiesList.Add(company);
}
}
return companiesList.ToArray();
}
That’s all folk!
