Join with DatePart
Joined: 20-Apr-2006
Joined: 20-Apr-2006
Joined: 20-Apr-2006
Joined: 20-Apr-2006
jgombala wrote:
Otis wrote:
This error occurs due to the nested subquery in the projection: as it is executed separately, it has to know how to relate rows produced by that nested query to the rows in the outer query.
You pass in the key of the group by, the code overlooks that as it only works with field compares at this stage because the groupby key isn't in the projection of the outer query, it can't make this workable: how to compare a row in the outer set to a row in the inner set?
Creating efficient nested queries is a complex issue, and therefore we first supported nested queries with normal field - field comparison correlation filters, as the rest is very complex to do (if not impossible, as the correlation you specify above is undoable in a query as the values to compare with aren't in the projection of the outer query).
Understood and makes sense. I would, however, assume you would compare the outer set to the inner set on the grouped by date. I am, just for the record, able to take the same query and run it in LINQPad with out any issues. What it appears is that the Linq To SQL implementation is taking the "where a.ActivityDate.Date == new DateTime(g.Key.Year, g.Key.Month, g.Key.Day)" and converting it to SQL DatePart functions.
The main difference is that Linq to Sql doesn't do any nested query logic. It simply executes for every row in the resultset, the nested query again, so if your outer query results in 1000 rows, it will run 1000 times the nested query. As you can see, with 3 rows in the outer query, it will run the nested query 3 times.
We didn't opt for this scenario as it's incredibly inefficient, and it requires a lot of extra code in the provider (and generated IL classes have to be able to execute queries on the fly).
Otis wrote:
I also asked a question about 'Value', you ignored it, though it's key that you are consistent towards us what you're testing and what goes wrong exactly. we don't have the code you 're using in front of us, so all we have is what you post here. The query above in the previous post with '.Value' on the datetime field is confusing.
Wasn't ignoring it... I didn't use 'Value'. That was a suggestion from someone else in this post [daelmo] that I try that to fix my issue. You'll see in all the queries I posted that 'Value' wasn't specified.
Ah 
My appologies.
Otis wrote:
Btw, your initial quest: "I am trying to get all the activity for a particular UserId based on the Date it was entered." Is that: filtered on a particular date, or grouped by date?
Grouped by the Activity Date for a particular UserId. Not for a particular date.
Ok, understood. Indeed then the query makes perfect sense (the last one, the first one you posted is extremely complex and not necessary). I'll report back when I've done the necessary changes to make yuor query (and other similar constructs) work.
Joined: 20-Apr-2006
Arg.... I see, my test was wrong! What a blunder... .
You of course need the Date function call because you want to filter out the Time part of the values to group on...
Will look into this as well. Sorry for this inconvenience.
Joined: 20-Apr-2006
Hmm, this is very tough to solve. The problem is the '.Date' property which is converted to a DB construct. '.Date' is an existing .NET property with actual code and meaning in memory. However you can map this Date property onto another db construct or use a .NET extension method on DateTime you wrote yourself and a custom mapping and group with that. This will give a grouped set with the key values returned. However, to merge the two sets in memory, the group by key lambda has to be applied on each entity returned, in memory. This will give different results, in the case where you used a custom mapping and probably different results if you used the default mapping but where tiny differences are present between DB execution/function and in-memory function.
The query processing now goes wrong where it has to build a pair of fields to compare between outer and inner query, and the field on the inner query is the db function call result, which of course doesn't match a field in the entities in the inner query: it has to perform a compiled version of the lambda on the entity and use the result of that in the comparison. The problem is: the lambda can't always produce the result as it's ran in-memory.
As outer query, the engine builds: select [key], [constant] from [group by query]
as inner query, it builds: select ... from [source of group by] where [keyfield1] in (values...)
It is possible to work around the issue and build the query though the problem arises where the two sets have to be merged. This is done by using the group of field pairs to field all elements in the inner query for a given row in the outer query. These are then placed in the slot of the constant and the whole set of rows of the outer query is then converted into a list of IGrouping<keytype, entity> objects.
The problem is to find a way to tie a row in the inner query to a row in the outer query. With a grouping on a field, that's easy, the field exists. With a grouping using a function call, it's not possible without running the function call in-memory as well and with that hope that the lambda which caused the function call to appear, produces the same result, which isn't guaranteed.
I don't see a reliable way to solve this particular case at this point. The query: var a1 = (from t in db.CustActivity where t.UserId == userId group t by t.ActivityDate.Date into g select new BusinessEntities.Activity.List { Date = g.Key }).ToList();
should work though...
I was hoping it would be possible though at the moment I don't see a way to do it, besides going the route of the slow inefficient way of how linq to sql does it.
If you really need this, you could go the low-level route and add a computed column to the CustActivity table which returns the date fragment of the ActivityDate field, you can then group on that field and return 'g', which works as the field is present in the entity. 
(to grab the date fragment, do: DATEADD(ms, -DATEPART(ms, [LPLA_1].[OrderDate]), DATEADD(s, -DATEPART(s, [LPLA_1].[OrderDate]), DATEADD(n, -DATEPART(n, [LPLA_1].[OrderDate]), DATEADD(hh, -DATEPART(hh, [LPLA_1].[OrderDate]), [LPLA_1].[OrderDate])))) )