Linq to sql - get value from db function and not directly from the db field (while mapping properties)

Question

When you map a table to an object, every property created corresponds to one db column. I want to execute a db function on a column before it gets mapped to the property, so the property gets the value returned by the db function, and not the column

I was trying to achieve that by Expression property of ColumnAttribute (as in the example below), so instead of BirthDate the usrFn_UTCToLocalTime(BirthDate) is returned but it does not seem to be working and still gets pure value of the column.

[global::System.Data.Linq.Mapping.ColumnAttribute(Storage = "_BirthDate", DbType = "DateTime", UpdateCheck = UpdateCheck.Never, Expression = "dbo.usrFn_UTCToLocalTime(BirthDate)")]
    public System.Nullable<System.DateTime> BirthDate
    {
        get
        {
            return this._BirthDate;
        }
    }

I have also modified the DBML XML as in: other post on stackoverflow but also without result.

Is that possible by using LINQ or do I have to overwrite a getter which costs roundtrip to the server?

WarrenG · Accepted Answer · 2012-10-25 08:35:46Z

0

According to the Remarks section on this MSDN page, the Expression property of the ColumnAttribute is used when calling CreateDatabase, so it won't work the way you intend unless you created your database with Linq to Sql.

You can create a Linq query that selects the various columns and calls the db function in one statement like this (based on MSDN example):

var qry = from person in db.Persons
          select new {
              FirstName = person.FirstName,
              LastName = person.LastName,
              BirthDate = person.BirthDate,
              Dob = db.usrFn_UTCToLocalTime(person.BirthDate)
          };

This projects into an anonymous type, but you could use a non-anonymous type as well. For the sake of the example, I've got a table named Person with FirstName, LastName, and BirthDate columns, and a user defined scalar function named usrFn_UTCToLocalTime. The sql statement sent to the server is:

SELECT [t0].[FirstName], [t0].[LastName], [t0].[BirthDate], CONVERT(DateTime,[dbo].[usrFn_UTCToLocalTime]([t0].[BirthDate])) AS [Dob]
FROM [dbo].[Person] AS [t0]

answered Oct 25, 2012 at 8:35

WarrenG

3,08418 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Johnny Over a year ago

Thanks for the answer. But this is not my goal. The thing is that everybody who is going to use the LINQ object must get the proper value transparently, without knowledge that the db function must be used. Explicit calling this function with every access to the object is error prone and can be forgotten. I will rather overwrite the getter as I was suggesting in the question (but I wanted to avoid the roundtrip to the server)

Johnny · Accepted Answer · 2012-10-25 10:51:49Z

0

As I was suggesting in the question, for now I have overwritten the get method so I have:

get
        {
            using (var context = DB.Data.DataContextFactory.CreateContext())
            {
                return context.usrFn_UTCToLocalTime(_BirthDate);
            }

            //return this._BirthDate;
        }

But with every access to the property, roundtrip is made - which is not my intention but gives a proper result. I leave the question still open

answered Oct 25, 2012 at 10:51

Johnny

4253 silver badges15 bronze badges

Collectives™ on Stack Overflow

Linq to sql - get value from db function and not directly from the db field (while mapping properties)

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related