Since .NET evolves with each new release, we recently took a look at how data handling has changed in the later versions of .NET since we typically have been using direct SQL communications through the SQL client. Getting some familiarity now will be important since Epicor is utilizing a data model with linq for performing queries in the new E10.

Entity Framework

In .NET 3.5 Microsoft introduced Linq2SQL which allowed developers to perform linq queries against a SQL database (treating the tables as objects). But that implementation was just a stepping stone to the new data model which was added in 3.5sp1 and is vastly improved in 4.0, called Entity Framework.

Entity Framework allows you to either (1) create the tables first and then it can automatically create the supporting classes, or (2) you can create the classes first and then it will automatically create the supporting datatables. I will cover one way, but its important to note that this just scratches the surface...Entity Framework is MUCH deeper.

Lets Look at an Example

So, I want to create a user account that can have multiple profiles associated with it. Since I am doing code first, I can just dive in and create the associated objects:

In terms of objects, when I retrieve a User object I have a collection of Profiles existing for that User object, or if I have a Profile I can easily access the owning User object.

Now the power behind Entity Framework is that by specifying all of the database qualifiers and that they are also objects, it will prevent me in my code from making changes that the database will be unable to accept. But remember, we’re not working directly with the database (it’s a disconnected model). So if I specify using a qualifier that the format of a column is a telephone number, the object will require me to interact with it as such instead of producing an error when I try to write it back to the database if the format does not match.

So now that we have the POCOs (Plain Old CLR Object) created we create a class for the database interaction.

public class Login : DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Profile> Profiles { get; set; }
}

Now to do this, you will need to use NuGet to get Entity Framework and either add (or resolve, cool feature) using System.Data.Entity;

Now this is the bare minimum. You can add multi-keys, control what the database name is, control how writes are performed, etc., but this gets us going. It will automatically create the database, tables, etc. for us based off of our coding (you control where by a connection string named the same as the database object). And we can simply treat it as an object so lets say…

Login lgn = new Login();

User usr = new User
{
userID = 1,
userName = "bellis1",
fullName = "Brian Ellis1"
};
lgn.Users.Add(usr);

So I’m creating an instance of the database object, and creating an instance of the table object, specifying the fields, and saying add that object to the database. But if you only had this code, it would not save back to the database because the data is disconnected you also need to have:

lgn.SaveChanges();

That commits the changes made to the database.

Now there are a lot of ways to query and find records in the database. A simple way is using:

Lgn.Users.Find(1);

Which would return the user object with the key value of 1.

Or since it’s an object, we can use linq to query the database:

var res = from u in lgn.Users
select new { u.userID, u.userName };

So since I don’t have a where clause, this would pull a new array of userid and username from the Users table. For good measure, let’s say this was a web service, we could then serialize the result using JSON and send it on its way:

JavaScriptSerializer jss = new JavaScriptSerializer();
var res2 = jss.Serialize(res);

Conclusion

Entity Framework is a very flexible and powerful way to interact with databases. Since it utilizes a connection string it supports many databases and would require no programming changes to shift between database servers. Now in my code I am telling it to drop and recreate the database if there is a structural change. So if I add a new column to the coding (which related to the database) since I am in development, I just drop and recreate. But another incredibly powerful feature of Entity Framework is that it will automatically produce uplift code for each database change that you make (via code). So you can step production databases up through your new changes (they are like patch files that are created, your code then can call them on run to detect the database version, code version, and apply the patches necessary using a single line).

Do you have any creative uses for .NET's Entity Framework?  Drop us a line and let us know!