Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> Visual Studio Orcas >> ADO.NET Orcas

linQ not working in simple tasks

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

DBOEODataContext db =newDBOEODataContext();

var customerList= (from customerin db.PERSONs

where customer._OWNERID == 37

where customer._DELETIONSTATUS == 0

where customer._TYPE =="ALICI"

orderby customer._LASTMODIFYDATE

selectnew

{

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?

[1786 byte] By [www.ilkon.com] at [2008-1-9]

«« sending fax
»» WlanSetProfile() Can''t Work in XP sp2.

# 1

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(); }

}

KevinHoffman at 2007-10-3 >

top of Msdn Tech,Visual Studio Orcas,ADO.NET Orcas...

# 2

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....

www.ilkon.com at 2007-10-3 >

# 3

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

AndersHejlsberg at 2007-10-3 >

# 4

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

www.ilkon.com at 2007-10-3 >

# 5

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.

VikramLakhotia at 2007-10-3 >

# 6

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

www.ilkon.com at 2007-10-3 >

# 7

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

RicoAlexander at 2007-10-3 >

# 8

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.....

www.ilkon.com at 2007-10-3 >

Visual Studio Orcas Hot Topic

Visual Studio Orcas New Topic

Visual Studio Orcas

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Question about 'linQ not working in simple tasks', No.2103527,Posted by www.ilkon.com

linQ not working in simple tasks

Visual Studio Orcas Hot Topic

Visual Studio Orcas New Topic

Visual Studio Orcas

Site Classified