http://www.ilkon.com

I wonder why does linQ do not work in simple scenerios like converting date..

DBOEODataContext db = new DBOEODataContext();

var customerList= (from customer in db.PERSONs

where customer._OWNERID == 37

where customer._DELETIONSTATUS == 0

where customer._TYPE == "ALICI"

orderby customer._LASTMODIFYDATE

select new

{

FULLNAME = customer._NAME + " " + customer.LASTNAME,

TEL = customer.MOBILETEL,

LASTCONTACTDATE = customer._LASTMODIFYDATE.ToShortDateString()

}).Take(10);

GridView1.DataSource = customerList;

GridView1.DataBind();

>>>>>>>>>>>>>>>>>>>>>

I get an error.......Member 'System.DateTime.ToShortDateString' could not be translated.

what is the "simplest" turn around for this




Re: ADO.NET (Pre-release) linQ not working in simple tasks

Kevin Hoffman

Don't forget that you're working against an underlying data model here, so when you're making those LINQ queries, what's actually happening is you're building an expression tree that will then be parsed and turned into the native execution language of whatever underlying provider you have. In this case (and in all cases right now), that language is SQL. In short, when you try and call _LASTMODIFYDATE.ToShortDateString() , it might look syntactically correct, but what's really happening is that the parser converting the expression tree to SQL has no SQL equivalent for ToShortDateString().

What I have done in this situation is instead of using a dynamic, on-the-fly data type, I've created a concrete class with the properties needed, and used a property like this to compensate for the fact that some things don't translate mid-query:

public property LastContactDate

{

get { return _lastModifyDate.ToShortDateString(); }

}






Re: ADO.NET (Pre-release) linQ not working in simple tasks

www.ilkon.com

it might look syntactically correct, but what's really happening is that the parser converting the expression tree to SQL has no SQL equivalent for ToShortDateString().

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

I guess anders heilsberg was just telling stories about LinQ will enable us to tell "WHAT" in stead of "HOW".

we are still stuck with an imperative thing that is not much usefull at all

also your alternetive also does not work with the anoymous types!!!!!!

Binding error : Member 'PERSON.LastContactDate' is not a mapped member of 'PERSON'.

I am having serious concerns about migrating to linQ....






Re: ADO.NET (Pre-release) linQ not working in simple tasks

Anders Hejlsberg

When the source of a query is a database table, the query is translated to SQL and executed remotely. There are limitations to what can be translated, and ToShortDateString() is indeed one of those methods that don't have a translation. If you tried to call user defined methods in your select clause it would be the same situation.

The general solution to use AsEnumerable() to break the query into two parts, one that executes remotely and one that executes locally.

Code Snippet

var remoteQuery =

( from p in db.Persons

where ...

select new { p.FirstName, p.LastName, p.Phone, p.LastModifyDate }

).Take(10);

var localQuery =

from p in remoteQuery.AsEnumerable()

select new {

FullName = p.FirstName + " " + p.LastName,

p.Phone,

ContactDate = p.LastModifyDate.ToShortDateString()

};

The AsEnumerable() method returns the IQueryable<T> as an IEnumerable<T>, which means the remainder of the query is executed locally.

Anders





Re: ADO.NET (Pre-release) linQ not working in simple tasks

www.ilkon.com

once a wise man told me

every problem can be solved by just one more level of indirection.*

I got your point but this doubles all my code...

PS:*not too much indirection






Re: ADO.NET (Pre-release) linQ not working in simple tasks

Vikram Lakhotia

But dont you think this way we are going to more number of records from the database. It can make a huge difference in the performance. Lets say I have 10000 Records that Match the first query and only 10 records matching the below condition.

Code Snippet

ContactDate = p.LastModifyDate.ToShortDateString()


This can be huge performance issue as now LINQ will bring 10000 records from database and then provide me back only 10 records. But What should happen is the We should Bring only 10 records from database.





Re: ADO.NET (Pre-release) linQ not working in simple tasks

www.ilkon.com

even linQ to sql has down sides it is a tradeoff.....I much prefer ling than datasets.....

I use datasets 15 percent

dynamic sql 20 per

ajnd the rest linQ to SQL......I need to realize when to fold Smile






Re: ADO.NET (Pre-release) linQ not working in simple tasks

Rico Alexander

Out of curiosity when do you use datasets as oppose to linq to sql.





Re: ADO.NET (Pre-release) linQ not working in simple tasks

www.ilkon.com

hmmm ok ..

I only use datasets in the report viewer and where use dynamic sql

LinQ to SQL is much more readable so I have refactored object based working places such as all my detail forms in the CRM application and CRUD operations are all in LinQ to SQL.

but there are times that linQ to sql can not solve such as dynamic queries has a bug that has not been fixed I used typed datasets there.....