Calculated Columns in the Entity Framework?

I've not yet got much experience with ORM's, and on my last ASP.NET Web Forms site, a friend created a mapping file (for nHibernate) which contained a couple of calculated columns. These 2 columns mapped to a couple of properties for my 'Photo' model.

Now I'm re-creating the same small website, albeit using ASP.NET MVC and so far have used the Entity Framework, however, I need to accomplish this same thing, map these 2 calculated columns to a couple of properties for my 'Photo' model, but can't see how to do it using this framework.

Here is some of the content of my original nHibernate mapping file for the Photo object.

<class xmlns="urn:nhibernate-mapping-2.2" name="Photo" table="Photos">
<id name="Id" unsaved-value="0">
  <column name="PhotoId" />
  <generator class="native" />
</id>
<many-to-one name="Lens" not-null="true">
  <column name="LensId" />
</many-to-one>
<property name="Title" not-null="true">
  <column name="PhotoTitle" />
</property>
<property name="Description" not-null="true">
  <column name="PhotoDescrip" />
</property>
<property name="TotalPhotosInMonth" formula="(SELECT COUNT(p.PhotoId) FROM Photos p WHERE p.DateTimePosted BETWEEN DATEADD(month, DATEDIFF(month, 0, DateTimePosted), 0) AND DATEADD(second, -1, DATEADD(month, DATEDIFF(month, 0, DateTimePosted) + 1, 0)))" />
<property name="TotalPhotosInYear" formula="(SELECT COUNT(p.PhotoId) FROM Photos p WHERE p.DateTimePosted BETWEEN DATEADD(year, DATEDIFF(year, 0, DateTimePosted), 0) AND DATEADD(second, -1, DATEADD(year, DATEDIFF(year, 0, DateTimePosted) + 1, 0)))" />
 </class>

It's the last 2 properties (TotalPhotosInMonth & TotalPhotosInYear) in this mapping file that I'm ultimately trying to accomplish using the Entity Framework (where I simply want to retrieve the total number of photos in the DB for that month/year).

Can anyone please advise how I can do this using Entity Framework?

Answers


You can try to do the trick using DefiningQuery:

<DefiningQuery>
  SELECT photoid, lensid, phototitle, photodescrip, (SELECT COUNT(p.PhotoId) FROM Photos p WHERE p.DateTimePosted BETWEEN DATEADD(month, DATEDIFF(month, 0, DateTimePosted), 0) AND DATEADD(second, -1, DATEADD(month, DATEDIFF(month, 0, DateTimePosted) + 1, 0))) as totalphotosinmonth, (SELECT COUNT(p.PhotoId) FROM Photos p WHERE p.DateTimePosted BETWEEN DATEADD(year, DATEDIFF(year, 0, DateTimePosted), 0) AND DATEADD(second, -1, DATEADD(year, DATEDIFF(year, 0, DateTimePosted) + 1, 0))) as totalphotosinyear from photos
</DefiningQuery>  

Please note that the entity will be read-only unless you specify Insert, Update and Delete stored procedures for it.


Need Your Help

Recursively find dynamically created controls inside panel

c# asp.net

I know that similar questions have been asked but unfortunately none of them answer my question. These answers either are working with defined number of child controls or are telling to get any spe...

jQuery event delegation and handlebars DOM elements

jquery ruby-on-rails dom handlebars.js event-delegation

So I've had this problem for a few hours, and I've researched all the other answers and they all say that it has to do with assigning an event to a dom element that doesn't exists yet. The weird t...