CODEWITHSUNDEEP
linqdatetimenullable
matthew_360
matthew_360

Reputation: 6061

Why can't I cast nullable DateTime as string in a LinQ query?

I am trying to take a DateTime value, and if it is not null return the Short Time String. My query looks like this: (TimeIn is NOT NULLABLE, whereas TimeOut is NULLABLE)

    var times = from t in db.TimePostings
                where t.MemberID == member.MemberID
                select new
                {
                    Date = t.TimeIn.ToShortDateString(),
                    TimeIn = t.TimeIn.ToShortTimeString(),
                    TimeOut = t.TimeOut.HasValue ? t.TimeOut.Value.ToShortTimeString() : "-------"
                };
gvTimePostings.DataSource = times;
gvTimePostings.DataBind();

but this fails when I try to databind with the error:

Could not translate expression 'Table(TimePosting).Where(t => (t.MemberID == Invoke(value(System.Func1[System.String])))).Select(t => new <>f__AnonymousType84(Date = t.TimeIn.ToShortDateString(), TimeIn = t.TimeIn.ToShortTimeString(), TimeOut = IIF(t.TimeOut.HasValue, (t.TimeOut ?? Invoke(value(System.Func`1[System.DateTime]))).ToShortTimeString(), "-------"), Hours = ""))' into SQL and could not treat it as a local expression.

I also get a similar error if I try to use:

TimeOut = t.TimeOut.HasValue ? Convert.ToDateTime(t.TimeOut).ToShortTimeString() : "-------"

however, if I change the TimeOut property to:

TimeOut = t.TimeOut.HasValue ? t.TimeOut.ToString() : "-------",

it works fine, but does not format the time like I want it (shortTimeString).

what's up with that?

Upvotes: 9

Views: 8144

Answers (6)

fabiuz
fabiuz

Reputation: 293

I had the same problem in a project in vb.net. The solution I've found is based on the use of:

if(table.field.hasvalue, table.field.value.ToShortDateString, string.format("NULL"))

In this case, if the selected field (table.field) has a value this is converted into a date string otherwise if the field hasn't a value the output field is filled with string "NULL"

Upvotes: 0

Andrew Shepherd
Andrew Shepherd

Reputation: 45222

Simply, it's not supported by this specific linq provider.

Your linq query is converted into an expression tree. It is up to the SQL Linq provider to convert this expression tree into SQL. Understandably, it does not have the capability to translate every single .NET function.

Your solution is to explicitly run the SQL by calling ToArray or ToList, and then allow LinqToObjects to handle the rest. 

   var times = from t in db.TimePostings
            where t.MemberID == member.MemberID
           select new { 
                        TimeIn = t.TimeIn,
                        TimeOut = t.TimeOut
                      };

   var timesFormated = times.ToArray()   // Runs the query - any further processing will be run in memory by the local .NET code
                        .Select(t => new {
                                           Date = t.TimeIn.ToShortDateString(),
                                           TimeIn = t.TimeIn.ToShortTimeString(),
                                           TimeOut = t.TimeOut.HasValue ? t.TimeOut.Value.ToShortTimeString() : "-------",
                                          Hours = ""                                               
                                         }
                                );

Upvotes: 2

Justin Niessner
Justin Niessner

Reputation: 245399

ToShortTimeString() has no translation in SQL. Because of that, converting the statement into a single SQL statement fails and the exception is thrown.

If you break the statement into two calls (one to retrieve the data and another to create the projection), things will work just fine:

// must call ToList to force execution of the query before projecting
var results = from t in db.TimePostings
              where t.MemberID == member.MemberID
              select new { t.TimeIn, t.TimeOut };

var times = from t in results.AsEnumerable()
            select new
            {
                Date = t.TimeIn.ToShortDateString(),
                TimeIn = t.TimeIn.ToShortTimeString(),
                TimeOut = t.TimeOut.HasValue ? 
                    t.TimeOut.Value.ToShortTimeString() :
                    "-------"
            };

Upvotes: 6

Jon Skeet
Jon Skeet

Reputation: 1499730

As others have said, the problem is with trying to convert ToShortDateString etc to SQL. Fortunately, this is easy to fix: fetch the data with SQL, then format it in .NET:

var timesFromDb = from t in db.TimePostings
                  where t.MemberID == member.MemberID
                  select new { t.TimeIn, t.TimeOut };

var times = from t in timesFromDb.AsEnumerable()
            select new
            {
                Date = t.TimeIn.ToShortDateString(),
                TimeIn = t.TimeIn.ToShortTimeString(),
                TimeOut = t.TimeOut.HasValue 
                                     ? t.TimeOut.Value.ToShortTimeString() 
                                     : "-------"
            };

The call to AsEnumerable() here basically means, "stop trying to process the query using SQL; do the rest in LINQ to Objects".

Upvotes: 10

ChrisF
ChrisF

Reputation: 137108

Have you tried:

TimeOut = t.TimeOut.HasValue ? t.TimeOut.ToString("d") : "-------",

This will normally give the short format of the DateTime. Whether it works or not will depend on whether it can be translated to SQL or not.

If it doesn't work you'll have to break the query into two parts. The first gets the data, the second format it. You'll have to convert the first query to a list (.ToList()) to force the SQL to be evaluated.

Upvotes: 2

Alexander Galkin
Alexander Galkin

Reputation: 12524

Your query is transformed by LINQ to an SQL that is fired against your database, and there is obviously no way to translate t.TimeOut.Value.ToShortTimeString() to SQL.

Possible solutions are:

  1. First fetch your data from database (by calling .ToList() or .ToArray() on your LINQ query), that converts your IQueryable<> into IEnumerable<> and then apply your transformation for every row fetched.
  2. Use a view that takes the original table and performs the conversion using CONVERT() function on the SQL Server and use it as the source for your Linq-to-SQL class. That would be performanter, but requires some server-side changes.

Upvotes: 1

Related Questions