2

I'm trying to get the Day of the week from a Nullable DateTime and then Joining on a list of days.

My solution was to convert it to a DateTime first, but Linq to Entities doesn't like it.

LIST is an IEnumerable<string>

suggestions?

        var result = from X in model.X
                     where X.StartDate.HasValue
                     join item in LIST on
                        Convert.ToDateTime(X.StartDate).DayOfWeek.ToString() equals item
                     select X;

Converting to a methods chain is no help:

var result = model.X.Where(x => x.StartDate.HasValue).Join(LIST,x => Convert.ToDateTime(x.StartDate).DayOfWeek.ToString(), item => item, (x, item) => x);
KevinDeus
  • 11,988
  • 20
  • 65
  • 97

4 Answers4

2
var result = from X in model.X
             where X.StartDate.HasValue && 
                   List.Contains(SqlFunctions.DatePart("weekday", X.StartDate))
             select X;

weekday returns an int so you should have a list of integers not strings

Magnus
  • 45,362
  • 8
  • 80
  • 118
  • 1
    +1, but be careful - the value returned by `DATEPART` will change if the server's `@@DATEFIRST` has been modified. You can also use `SqlFunctions.DateName` to get the day of the week as a string. – Jeff Ogata Oct 10 '11 at 22:10
  • 2
    Your right, but if you use DateName you have to worry about culture – Magnus Oct 10 '11 at 22:13
  • Hi i m using this but getting error NotSupportedException: Method 'System.Nullable`1[System.Int32] IsNumeric(System.String)' has no supported translation to SQL.] in my code what to do next to resolve this . – Pranay Rana Aug 08 '12 at 09:38
1

this will work if you use .ToList() before the convertion take place. Entity 4.0 Casting Value As DateTime

var result = model.X.Where(x => x.StartDate.HasValue).ToList().Join(LIST,x => Convert.ToDateTime(x.StartDate).DayOfWeek.ToString(), item => item, (x, item) => x);
Community
  • 1
  • 1
Jaider
  • 14,268
  • 5
  • 75
  • 82
  • this will "make it work", but your link explains it best: "the downside is when you call .ToList() this will bring all the data from db and then do the conversion in host side instead of database side" --not exactly what I was after. – KevinDeus Feb 06 '12 at 07:30
1

.ToString() is not supported by Linq-To-Entities, as are a number of other extension methods.

The short (and probably inaccurate to some degree) reason is that the .ToString() in the context above is sent to Sql Server to run in a query. Sql Server has no idea what .ToString() is, and so it fails when Sql Server's query parser tries to execute it.

also, FYI

http://msdn.microsoft.com/en-us/library/bb738550.aspx

KevinDeus
  • 11,988
  • 20
  • 65
  • 97
0

EDIT: Do not use this code. It has been left here to show you what will not work and so you can see Chris' reasons why and how to solve.

Untested:

    var result = from X in model.X
                 where ((X.StartDate != null) && X.StartDate.HasValue)
                 join item in LIST on
                    Convert.ToDateTime(X.StartDate).DayOfWeek.ToString() equals item
                 select X;

If you have declared StartDate as a Nullable field, it follows you should check for null values.

  • 1
    What's the difference between `X.StartDate != null` and `X.StartDate.HasValue`? – Gabe Oct 10 '11 at 21:59
  • Ask yourself this: What properties does `null` have? @KevinDeus: It is simple enough to try and see. –  Oct 11 '11 at 12:55
  • Are you suggesting that is `X.StartDate` is null then you can't get its `HasValue` property? If so, I suggest you try it yourself with a `DateTime?` instance. – Gabe Oct 11 '11 at 16:05
  • I dont need to run that query, LinqToEntities doesn't like .ToString(). its not supported – KevinDeus Oct 11 '11 at 17:39