Beginner Question : How to add missing properties

Question

Hey!

Say that I got a forum table and a user table in my MS SQL 2008 server database. Now I will fetch some records from the forum table but this table includes columns like userId and so on. How do I manage to also get the UserName from the user table in to my entity?

1. I could add a userName column to the forum table, this will be a double storing of the name but may be the best solution for this scenrio (performance).
   
   
2.  Say that I do not want to double store the information, could I mabe add the property to the entity and map it to the user table?

How do you handle this?

BestRegards

Answer 1

You need to first make a relationship beetween Users and Forums in your database, if that is your case already. Then just create the EDMX model, mapper will do the job for you.

Aside from that you need to be more specific, is this a Web or Winforms project?

---

VB.NET to C# http://www.developerfusion.com/tools/convert/vb-to-csharp/

Answer 2

I have the follwing layers :

PLL = Webproject (MVC)
BLL = Class library
DAL = Class library

I am using Entityframework in the DAL to generate the entities from the database. This means that the objects of the entities from the DAL are transfared up to the webproject where it is displayed.

I do have relationss in my database already.

Say that I featch the Forum entite from database, this object are transfared to the webproject. Say that I now want to ge additional information like creater names or even replys that belongs to the forum object. This can be done by digging my way down in the forum entity, but is this realy the right way when I am in the PLL? Will this not mean that new SQL commands will be runned against the database direcly from my entity that I have got to the PLL?

BestRegards

Answer 3

 One way would be to load related ends imidiately when you query for Forum entity, then both Forum and child entity (User, Reply) will be transferend to your PLL.

---

VB.NET to C# http://www.developerfusion.com/tools/convert/vb-to-csharp/

Answer 4

But this will mean that I load alot of data that may not be shown right?

What happens if I do trie to reache example username from the forum entity in the PLL? forum.UserInformation.Name

Answer 5

Well if you havent loaded UserInformation it will not show, EF does not lazy load relations. You could then do a simple LINQ query that will return only UserInformation.Name

---

VB.NET to C# http://www.developerfusion.com/tools/convert/vb-to-csharp/

Answer 6

Okay so your sugestions is to for every userId i got I will do a call to the DAL that makes a LinQ request to the database? This simes like a slow solution if the community grows a bit. UsersId are ofcource not the only ids that will have to be featched this way.

Could I not just add the userName propertie to the forum entity? I do only want to load this when I am in the DAL, when it reaches the PLL the informations should already be there. 

Answer 7

 You could but then you would have duplicate and misplaced data in your database.

---

VB.NET to C# http://www.developerfusion.com/tools/convert/vb-to-csharp/

Answer 8

Okay? so its not possible to just add a propertie that are not connected to the database to a EntityFramework entity? I do not really understand this, how do you solve this problem? You don´t whant to featch data that are not shown for the client and you do not whant to duplicate data in the database?

It sound like I should throw out the EntityFramework and instead build my own entities that I fill with LinQToSQL or likewize?

Answer 9

What is best practice in this case?

Answer 10

 A few comments:

1) If you use eager loading (the Include method) when you first retrieve the entity in order to retrieve the related entity, then you will retrieve extra data but it will all be done in one round trip to the database and often times round trips are a lot more important than some extra data.  This may be the easiest/most effective method for handling this.

2) You can add a property to the partial class of your entity which will be non-persisted.  In the getter for that property you could check if the related entity is loaded and call Load if not and then return the property from the related entity.

3) In the upcoming .net 4 release of the EF, it will be possible to set configuration info on a context which tells it to implicitly do lazy loading.  Then just navigating to the related entity will cause it to be retrieved if necessary. 

Options 2 & 3 gives reasonable performance if the frequency of needing the related entity is low enough that the extra round trip now and then is cheaper than the extra data all the time.

- Danny

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 11

Thanks!

To speed things up I have added the createrUserNickName to the forum class, this will ofcource mean that the data will be double stored, but sinse nicknames is somthing that you do not change is should be no problems. This will make the featch alot easier and faster.

This procedure also goes for lastReplyUserNickName and so on.

Is this a OK solution?

There will however be times when I need extra information from related tables in this cases I will probably use the eager loading but I will think twize becouse it will featch alot more data that means that the webserver need to have more memory I supose?

It is posible to add a createrUseNickName propertie on the forum class in the designer that are connected to the user table but this will mean that a join(or somthing like that) will be needed and this could mean slowdowns.

//Jimmy

Answer 12

Denormalizing properties from one table into another table to improve performance is certainly a common database technique.  You do have to be careful with it, so it's generally not the first thing that I would recommend, but it is a reasonable thing to do if you need.  The one bit of advice I have is to do this only after you have encountered a perf problem and decided that you really need it.  Often we optimize too early and correct the perf problems we think we are going to have rather than the ones we really have.

The same thing goes for the point of using eager loading.  For many, many cases this is the best solution and won't cause significant perf implications.  In some cases, though, the extra memory might be an issue, and I would recommend that you tackle that problem when you come to it.

Unfortunately you can't really map a single property out of a different entity into one entity using the designer because the EF goes to significant lengths to guarantee that you don't ever get data corruption by modifying the same thing in two places in different ways or the like.  That's why you have to be careful about denormalizing like this.  If you do it, then you need to add logic to your code to make sure that if the denormalized property ever does get modified you push the change back to the real source of the data and then to every place it is denormalized, etc.

- Danny

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 13

2) You can add a property to the partial class of your entity which will be non-persisted.  In the getter for that property you could check if the related entity is loaded and call Load if not and then return the property from the related entity.

Sorry for bumping an old thread, but how do you exactly do this? I added a custom property to my entity and was able to build just fine. However when I use that custom property in my query I get an exception:

The specified type member 'property name' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

I am using LINQ to Entities. Are custom properties only supported in LINQ to SQL or I need to do something else?
Thanks

Answer 14

In LINQ to Entities, the line between parts of the query which are pushed down to the server and parts hwich are executed locally in LINQ to Objects (as must be the case for a custom property like this which isn't persisted) is explicit.  The reason this line must be explicit with LINQ to Entities is that we want to be sure that you don't inadvertently create a query which works in a very unexpected way (like pulling a lot of data into memory and then filtering there when you expected it to filter on the server or something).

So the way you solve this is to separate the part of your query which operates on the server from the part that operates in memory and then use AsEnumerable() as the boundary between the two.  So, for example, if you have entity type Foo with property Bar on the server and Baz as a custom property that was added in the partial class, and you want to write a query that filters by a value of Bar and a value of Baz, then you could write something like this:

var serverQuery = from serverF in context.Foos
                  where serverF.Bar == "bar"
                  select serverF;
var inMemoryQuery = from inMemoryF in serverQuery.AsEnumerable()
                    where inMemoryF.Baz == "baz"
                    select inMemoryF

- Danny

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 15

Thanks for the explanation :)