luc debevere luc debevere - 1 month ago 10
SQL Question

Using database tables as objects?

I just started working on a existing (starting) project with the purpose of reorganizing it structurally and in performance as well. I usually work with EF as framework but i am familiar with Linq-to-sql and ADO as well. What i am seeing is that right now the web application is using the actual database tables & views as objects inside the code, so for example you have table 'foo' they would go on and use it as a object instead of using classes to pass trough data inside the app and finally inserting it into the DB/table. Since i never saw this and was not aware of it, i wonder, is this a "good" practice, i mean Microsoft did make it possible to do it like this so whats the "real" purpose, are there advantages/downsides ?

Thank you all for your responses !
Kind regards

Answer Source

So since you liked my comment I will expand. Let's say I have tables

Foo
FooId int 
Desc varchar(32)

Bar
BarId int
Desc varchar(32)

Now let's pretend I actually have many more and say 50 tables. I would not want to create all that by hand as it would be kind of nuts. Back in the day with ADO.NET you would create DataTables and potentially hand craft well formed objects. Well with Linq To SQL they made a document to handle most of the modeling for you but it was pretty limited to near identical to what the database looked like. But with Entity Framework MS took it a step farther. Now you have a database layer, an intermediary layer, and an object layer. Suffice to say you can even manipulate and create objects over and above what your 'context' wants.

But all that aside let's say I create an Entity Framework 6.1.3 model and select my database and select Database first. I get a model representation as I add objects and they are stored under a context for me. As far as EF is concerned these objects ARE the tables. You just do something like

using(var context = new DBContext())
{
    var newFoo = new Foo { Id = 1, Desc = "A" };
    context.Foo.Add(newFood);
    context.SaveChanges();
}

I just added and saved a new Foo object and I did not have to do ANY code creation of a POCO object. EF did that for me when running the custom tool by selecting the model object and right clicking and selecting 'Run Custom Tool'. Essentially this is just running the data to generate the POCO's and settings for you. Or code writing code in the T4. You can update the T4 to change settings but I don't recommend getting too crazy.

The method you are already going down is better. Just Wrap the code in new methods and voila you have a repository. The advantage of a repository over just doing say EF contexts inline is you can mix technologies or change them out as needed. The only thing I would say would be to keep the objects you use as what the EF or other technology generates. Or maybe you have some business logic with properties that auto invoke, well you don't want them to pollute your EF but they are fine in a repo pattern. Repository patterns IMHO should just be a mediator for your CRUD operations to sit between a retrieval technology and a calling portion.