0

How do I write this query in Linq to Entities:

select convert(datetime,data,103) from Audit where ActionId = 1

Column Data is of varchar(max) type. I know that if the ActionId equals one in a row, than the Data column in the row will always contain a string that represents a date in dd/MM/yyyy format.

Update:

I need to return the result of the L2E query as IQueryable, because paging / sorting is applied on top of it. This is another reason, why I want this field returned as DateTime - to be able to sort on it.

Andrew Savinykh
  • 25,351
  • 17
  • 103
  • 158
  • 1
    Why do you need SQL server to format datetime? Would it be easier to format in code? – Alex Aza Jun 16 '11 at 02:22
  • @Alex Aza: I'm happy with formatting it in code as long as it does not require looping through the whole recordset one more time. Do you know how to do this? – Andrew Savinykh Jun 16 '11 at 02:28
  • @zespri, no matter *what*, you *will* be looping through the returned record set at least once -- if you do *not*, why then of course you will not have consumed it at all. And of course, you can apply any projection you want with the `Select` operator to modify the data as you see fit. (Run it through `.AsEnumerable()` first so you aren't restricted to what can be translated into SQL.) Doing so will still only result in one pass. – Kirk Woll Jun 16 '11 at 02:30
  • @Kirk Woll: I'm not in control of the code that is going to be looping through this recordset later on. This code consumes me as an interface. I can't run it through enumerable, as I have to return IQueryable. Even if I could, it would still be running through the whole recordset an additional time and I would like to avoid this. – Andrew Savinykh Jun 16 '11 at 02:34
  • @zespri, I didn't mean you would be *literally* consuming it. If you assign the `Enumerable` to a datasource of a WinForms control, for example, some code will eventually loop through it. Using the `.Select` operator will not increase the number of passes. – Kirk Woll Jun 16 '11 at 02:36
  • If you have to return an `IQueryable` then doing it in code is a non-starter. (technically you can convert an `IEnumerable` into an `IQueryable` and make the compiler happy, but it won't really "work" -- no more SQL translation after passing through `IEnumerable`.) Why do you have to return an `IQueryable`? – Kirk Woll Jun 16 '11 at 02:37
  • @Kirk Woll: Select operator won't increase the number of passes but .AsEnumberable will. And I have to return IQueryable anyway. – Andrew Savinykh Jun 16 '11 at 02:38
  • @zespri, I agree that if you need to return an `IQueryable` then it's moot. But why do you suggest that `.AsEnumerable` will cost an extra pass? (The implementation of `.AsEnumerable` is `return source`) – Kirk Woll Jun 16 '11 at 02:39
  • @Kirk Woll, because the consumer applies paging and sorting on top of the query. – Andrew Savinykh Jun 16 '11 at 02:40
  • @Kirk Woll: correct me if I'm wrong, but when It's IQueryable it's a tree, when we do AsEnumerable it gets executed against SQL server. When you trying to `.Select` from it anyway. Thus, this is an extra pass. Is this wrong? – Andrew Savinykh Jun 16 '11 at 02:43
  • 1
    @zespri, `AsEnumerable` does not result in the query being executed. *Iterating* over it results in the query being executed. (for example, by using `foreach` or `.ToList`) Once the query is executed the data is returned in a state such that it can be iterated over (*one* time, whenever you `foreach` over it, etc.). Using the `.Select` operator simply instruments what happens on each iteration -- it applies a projection. But it doesn't change the number of times you loop through it. (However, there are many LINQ operators that *do* require multiple passes; `OrderBy` is a great example.) – Kirk Woll Jun 16 '11 at 02:47
  • @Kirk Woll: I think I'm starting to get what you mean. I didn't think that would work before, I'll give it a try. It won't solve my problem, but if it really works this way, than I learnt something today =) Basically you are saying that if I do AsEnumerable().Select() that won't lead to the query execution. Let me give it a try. – Andrew Savinykh Jun 18 '11 at 06:26
  • @Kirk Woll: Amazing. I tried this and it worked as you described. Never expected it to work this way. Thanks a lot! – Andrew Savinykh Jun 21 '11 at 09:56

2 Answers2

0

Would it be easier to format on client instead of server?

I would do something like this:

var audits = db.Audit
    .Where(arg => arg.ActionId == 1)
    .Select(arg => new { arg.Data })
    .AsEnumerable()
    .Select(arg => DateTime.ParseExact(arg.Data, "dd/MM/yyyy", CultureInfo.InvariantCulture)
    .ToList();

The data will be retrieved in string format.
After strings are retrieved they will be converted to DateTime.

Alex Aza
  • 76,499
  • 26
  • 155
  • 134
  • Since when L2E can do ToString? Am I completely misguided? Have you tried what you are suggesting with L2E? – Andrew Savinykh Jun 16 '11 at 02:18
  • Yes, this would certainly easier *to write*. I would like to avoid looping through this recordset one more time on the client, this is the reason, I want to do this on the server. – Andrew Savinykh Jun 16 '11 at 02:32
  • @zespri - why do you need to store formatted string in the received list? Could you just keep datetime in the object and format when you need to display it? – Alex Aza Jun 16 '11 at 02:35
  • @Alex Aza: I'm not sure that I understand your question, could you please clarify. The field in the database is string, because it can contain different types of data, depending on the record type. It is an Audit table that has different Audit event. Each Action has specific to this particular Action type data associated with it. For Action with ActionId of 1 it happens to be a date. – Andrew Savinykh Jun 18 '11 at 06:06
  • @Alex Aza: On the face value - you got it backwards: I do NOT need to have formatted string in the receiving list. And I do want to keep datetime in the object and format it when it has to be displayed. Looks like you read my SQL wrongly and in addition you probably missed the bit, that column 'Data' is of varchar(max). – Andrew Savinykh Jun 18 '11 at 06:13
  • @zespri - yep, I've got it backwards. My fault, didn't pay enough attention. Updated the answer. – Alex Aza Jun 18 '11 at 06:14
  • @Alex Aza: thank you. I'm going to update my question to explain why I can't as AsEnumerable, I have to return genuine IQueryable, so that the consumer of the code can apply paging and sorting to it. – Andrew Savinykh Jun 18 '11 at 06:19
0

I've found this: Convert String to Int in EF 4.0 It looks, like this trick might help, if we create a function to cast string into datetime.

UPDATE This problem left unsolved for me. As I needed a quick solution I converted the 'data' column to be of datetime type. This is not generic for future extensions, but it works for now. One of the solutions that are not really a solution.

Community
  • 1
  • 1
Andrew Savinykh
  • 25,351
  • 17
  • 103
  • 158