This thread looks to be a little on the old side and therefore may no longer be relevant. Please see if there is a newer thread on the subject and ensure you're using the most recent build of any software if your question regards a particular product.
This thread has been locked and is no longer accepting new posts, if you have a question regarding this topic please email us at support@mindscape.co.nz
|
I'm using LightSpeed to access a PostgreSQL table with the following structure: login_detail id uuid user_id text client_id text ...
I'm trying to mimic the following SQL: SELECT upper(UserId), ClientId, COUNT(*) FROM LoginDetail WHERE LoginTimeStamp >= fromDate AND LoginTimeStamp <= toDate (customerWhere) GROUP BY upper(UserId), ClientId
I wrote the following code to query the DB: var groups = from ld in uow.LoginDetails where ld.LoginTimestamp >= retFromDate && ld.LoginTimestamp <= retToDate && (retCompanyId == "" || ld.CompanyId == retCompanyId) group ld by new { ld.UserId, ld.ClientId } into g select new { g.Key, Count = g.Count() };
foreach (var group in groups) { LoginStatistics loginStatistics = new LoginStatistics {UserId = group.Key.UserId, ClientId = group.Key.ClientId, LoginCount = group.Count};
ret.Add(loginStatistics); }
LightSpeed generates the following SQL (which does not perform the Count()): SELECT g1.client_id, g1.user_id FROM ( SELECT login_detail.client_id AS "client_id", login_detail.user_id AS "user_id" FROM login_detail login_detail WHERE ((login_detail.login_timestamp >= :p0 AND login_detail.login_timestamp <= :p0) AND (:p1 OR login_detail.company_id = :p2)) GROUP BY login_detail.user_id, login_detail.client_id ) g1
When I try to process the first instance of group, I get an IndexOutOfRangeException from the FieldIndex() method in NpgsqlRowDescription.cs.
public int FieldIndex(String fieldName) { int ret = -1; if(field_name_index_table.TryGetValue(fieldName, out ret) || caseInsensitiveNameIndexTable.TryGetValue(fieldName, out ret)) return ret; else if(_compatVersion < GET_ORDINAL_THROW_EXCEPTION) return -1; else throw new IndexOutOfRangeException("Field not found"); }
The value of the fieldname parm to FieldIndex is "UserId". field_name_index_table does not contain "UserId" but does contain "client_id_ and "user_id".
My questions: 1) I couldn't figure out the correct LINQ syntax to perform an "upper(UserId)" 2) Why does the generated SQL not perform the Count()? 3) Am I correct that there is a LightSpeed bug that is calling npgsql with "UserId" (my property name) rather than the correct column name "user_id"? |
|
|
bump... |
|
|
Hi, In general you are going to find trying to "mimic" very specific SQL problematic, particularly using the LINQ provider since the conventions we use in building up queries are generic in nature and are not always going to be specific enough to allow for any arbitrary statement to be translated to, so for example combining the count(*) with the column selection is not something we would generate from our query pipeline. In general you can get better precision using the underlying query API. If you are looking for fine grained database queries, we would generally suggest you look at calling stored procedures if applicable - but otherwise you should probably be thinking in terms of your model rather than a database query :) To answer your questions above: 1. You will want to be calling .ToUpper() on Id.UserId in the grouping clause. This will be translated to the UPPER() function. 2. See above. There will be a separate count of the children performed as a seperate query with the LINQ grouping execution. 3. Yes, this sounds like a bug - which build of LightSpeed are you using? If you could send through a small repro sample of this behavior (model/code/sample data) and either email it or attach it to this post so we can have a look at it then we can review and fix this :)
Jeremy |
|
|
Jeremy, thanks for your reply. So far, I've found LightSpeed to be a great tool, but i'm clearly confused on its querying capability. I've read your response several times and I have even more questions. "you are going to find trying to "mimic" very specific SQL problematic" I'm converting from working code in SQLite to PostgreSQL. I have existing SQL statements that I want to convert to LINQ. That is what I mean by "mimic". In this particular case all I want to do is a GROUP BY that returns the count by grouping. It is not clear to me if LightSpeed provides me with the ability to GROUP BY and Count() using LINQ. Please confirm. "In general you can get better precision using the underlying query API." I'm sure I could use the underlying API but my primary reason to use LightSpeed is so that I can easily switch out underlying API without the need to update my code (as it will rely on LightSpeed to talk to the underlying API). "you should probably be thinking in terms of your model rather than a database query" This really confuses me... I'm using a simple model but i'm having errors querying the data out. 1. You will want to be calling .ToUpper() on Id.UserId in the grouping clause. This will be translated to the UPPER() function.
Cool, when I add the .ToUpper() the code compiles. Please confirm that the data will be uppercased when accessed as group.Key.UserId. 2. See above. There will be a separate count of the children performed as a seperate query with the LINQ grouping execution. I'm not sure what you mean by a "a separate count of the children performed as a seperate query with the LINQ". Will LightSpeed generate a second query or do I need a second query? 3. Yes, this sounds like a bug - which build of LightSpeed are you using? If you could send through a small repro sample of this behavior (model/code/sample data) and either email it or attach it to this post so we can have a look at it then we can review and fix this :) I'm using v 3.0.179.13186 (the most recent nightly build that was available last Friday). The problem also exists with the "official release" code (i'm not sure what version that code is though). BTW, when I try to preview my post it is not displayed. Also, when I copy code into a post it double spaces it and makes it hard to read. Thanks... Alan |
|
|
Hi Alan, Hope this provides some extra detail for you,
"I'm converting from working code in SQLite to PostgreSQL. I have existing SQL statements that I want to convert to LINQ. That is what I mean by "mimic". In this particular case all I want to do is a GROUP BY that returns the count by grouping. It is not clear to me if LightSpeed provides me with the ability to GROUP BY and Count() using LINQ. Please confirm." Yes, the query you have written will execute as you expect (you will get back a list of results, grouped by the grouping key you specified with a property containing the count of the results within each grouped collection). However the actual SQL being executed to fetch those results will span 2 queries (one for the initial grouping query, and then one to count the child collection results) rather than being the single query you indicated in the original post. To try and clarify, trying to replicate specific SQL statements is problematic because we decide how to make the translation. The same can be said of any LINQ query, since LINQ != SQL so its up to the provider how to translate from A to B. Do you actually need to replicate that EXACT statement, or just match the intent of it? I think thats what you are meaning to do and there is no problem in that - just that you might find that slightly different SQL gets generated to achieve the same result.
"I'm sure I could use the underlying API but my primary reason to use LightSpeed is so that I can easily switch out underlying API without the need to update my code (as it will rely on LightSpeed to talk to the underlying API)." Sorry, to clarify - I was meaning LightSpeed's underlying querying api (Mindscape.LightSpeed.Querying namespace objects, such as Query, QueryExpression) as opposed to writing your query using a LINQ expression. LINQ expressions are parsed by our LINQ provider into a LightSpeed Query object and then executed, so if you want more fine grained control you can construct your own Query objects. "I'm not sure what you mean by a "a separate count of the children performed as a seperate query with the LINQ". Will LightSpeed generate a second query or do I need a second query?" LightSpeed will add the second query automatically for you. This is handled within our LINQ provider so you dont need to do anything else to generate this. If you are able to send through the small repro sample for the issue you mentioned in the first post we can have a look into this for you.
Jeremy |
|
|
I've created a repro sample of the problem. How can I send it to you? Thanks... Alan |
|
|
Hi Alan, Thanks for spending the time to create this. You can send this through to jeremy @ the obvious domain name and we will have a look at whats causing the issue.
Jeremy |
|
|
Example was sent to you Jeremy. Thanks... Alan
|
|
|
Hi Alan, Unfortunately I havnt recieved anything yet - could you check if there were any errors or if the attachment got blocked?
Jeremy |
|
|
Just to add to Jeremy's comment Alan - we use Google for Domains which I think blocks attachments containing binary files (exe, dll, etc) even if they're in a zip file. Could you remove any of them from your zip? Alternatively, you could attach the zip to this thread (we ask that you please remove the lightspeed assemblies though). Hope that helps, John-Daniel Trask |
|
|
Hi Alan, Thanks for sending through the repro project. I have committed a fix for an issue which was causing the incorrect use of the field name rather than the column name in grouping attributes and this will be included in the next nightly build (20100326).
Jeremy
|
|
|
Jeremy, I've verified the field/column name fix and it works great!!! Thanks for the quick fix.
Unfortunately, I'm continuing to have a problem when I try to use a ToUpper() in the GROUP BY.
I get the following error: NotSupportedException was handled by user code Grouping contains unsupported arguments, such as binary expressions which are not supported in LightSpeed 3.0.
Specifically in my code, i'm getting the error on the following line: group ld by new { UserId = ld.UserId.ToUpper(), ld.ClientId }
It works fine as: group ld by new { ld.UserId, ld.ClientId }
You should be able to reproduce the problem by adding the ToUpper() to the code I sent you.
Thanks again for your help... Alan |
|
|
Hi Alan, As indicated with the exception message Method based arguments are not supported. However since this type of call is undoubtedly quite usual and useful I have added in the required support for this over the weekend and it will be available in the next nightly build (20100330) and above. Other types of arguments such as binary expressions are still not supported but we have a ticket on our backlog to review implementing this in the future. Let us know how you get on with the updates :)
Jeremy |
|
|
Jeremy, I'm sorry to hear that you worked on the weekend but I really appreciate it. THANKS!!! I updated to build 20100330 today and this problem has been solved. Thanks again... Alan |
|
|
I'm currently getting consistent but invalid results from g.Count(). When there is a single entry in the group I'm getting a count of 0. When there are more than one entries in a group i'm getting a count of 1. For Example: Data: Id, Value, Type 1, A, 1 2, B, 1 3, A, 1 4, C, 1 5, A, 1 If grouped by Value, Type, should return: A, 1, count 3 B, 1, count 1 C, 1, count 1 But the results I get are A, 1, count 1 B, 1, count 0 C, 1, count 0
Also... I was trying to use LINQPad to help me quickly experiment with LINQ. Unfortunately the Mindscape.LightSpeed.LinqPad.dll that is currently distributed for LINQPad is old (v3.3.3.13259 modified 3/9/10) and does not have the 2 fixes you made for this thread. Is it possible to get a daily build of Mindscape.LightSpeed.LinqPad.dll? Thanks... Alan
|
|
|
Hi Alan, Could you send through a small repro of the scenario you described above and we can have a look at what is going on there. The LinqPad driver actually uses whichever version of LightSpeed that you have built your model against so just make sure you have compiled the model which you are using within LinqPad against the current nightly build you have installed and then LinqPad will use that version of LightSpeed.
Jeremy |
|
|
I've emailed a repro of the scenario to you Jeremy. re: LinqPad, Is there a way for me to confirm what version of LightSpeed is being used? I'm getting an index out of range error that I believe is a problem that you have already fixed. I was getting that error when the npgsql contained a list of lower-case column names and LightSpeed was looking for the mixed-cased property name. But you fixed that error a couple of weeks ago. Thanks... Alan |
|
|
There isn't a direct way to confirm what version of LightSpeed is being used, because LINQPad should be picking up whichever version of LightSpeed your model assembly was last built against. But I think you should be able to write something like: typeof(IUnitOfWork).Assembly.GetName().Version in LINQPad's expression window and get the version that way. |
|
|
Thanks for the input Ivan, I had referenced an old version of my model which was built using an older version of LightSpeed. LINQPad and LightSpeed are playing well together now.
Thanks again... Alan |
|
|
Jeremy, I just wanted to confirm that you received my email with the repro of the Count() problem.
Thanks... Alan |
|
|
Hey Jeremy, I was hoping for a confirmation that you have received the email from me with the code to reproduce the remaining Count() problem.
Thanks... Alan |
|
|
Hi Alan, Jeremy is in the midst of moving house at the moment so he probably won't be able to respond today. I believe he should be back tomorrow. |
|
|
Hi Alan, Back on deck today and have received the repro - thanks for sending this through. I will be having a look at this today, will keep you posted.
Jeremy |
|
|
Hi Alan, As mentioned earlier, we have completed a fix for this issue which was to do with the use of functions on multi-part grouping keys. Happy to report that this will be included in the next nightly build (20100417) and above.
Jeremy |
|
|
Jeremy, i'm happy to report that the group/count() functionality is working great!!! :-)
I have found another scenario that causes an exception when using Join/OrderBy/Group functionality. The following works fine when I remove the OrderBy. Do you need me to create a repro test case for you? var groups = from sd in uow.SearchDetails join ld in uow.LoginDetails on sd.LoginId equals ld.Id where (sd.SearchTimestamp >= retFromDate && sd.SearchTimestamp < retToDate.AddDays(1)) && (retCompanyId.Length == 0 || ld.CompanyId == retCompanyId) orderby ld.UserId.ToUpper(), ld.ClientId group ld by new { UserId = ld.UserId.ToUpper(), ld.ClientId } into g select new { g.Key.UserId, g.Key.ClientId, SearchCount = g.Count() };
Thanks... Alan |
|
|
I will see if I can repro it based on the last sample, but could you post the exception details in the meantime? Thanks! |
|
|
Jeremy, I get an exception from the following code:
var groups = from sd in uow.SearchDetails join ld in uow.LoginDetails on sd.LoginId equals ld.Id where (sd.SearchTimestamp >= retFromDate && sd.SearchTimestamp < retToDate.AddDays(1)) && (retCompanyId.Length == 0 || ld.CompanyId == retCompanyId) orderby ld.UserId.ToUpper(), ld.ClientId group ld by new { UserId = ld.UserId.ToUpper(), ld.ClientId } into g select new { g.Key.UserId, g.Key.ClientId, SearchCount = g.Count() }; Here is the exception information:
BaseMessage: missing FROM-clause entry for table "login_detail0"
Code: 42P01
ErrorCode: -2147467259
ErrorSql:
SELECT
t0.search_id AS "t0.search_id",
t0.Hits AS "t0.Hits",
t0.login_id AS "t0.login_id",
t0.query_string AS "t0.query_string",
t0.response_time AS "t0.response_time",
t0.search_timestamp AS "t0.search_timestamp",
t0.Status AS "t0.Status",
t1.login_id AS "t1.login_id",
t1.client_id AS "t1.client_id",
t1.company_id AS "t1.company_id",
t1.login_timestamp AS "t1.login_timestamp",
t1.logout_timestamp AS "t1.logout_timestamp",
t1.session_id AS "t1.session_id",
t1.user_id AS "t1.user_id"
FROM search_detail t0
INNER JOIN login_detail t1 ON
t0.login_id = t1.login_id
WHERE
((((t0.search_timestamp >= ((E'2010-03-23 00:00:00.000000')) AND
t0.search_timestamp < ((E'2010-03-24 00:00:00.000000'))) AND
(((TRUE)) OR t1.company_id = ((E'')))) AND
UPPER(t1.user_id) = ((E'ALAN'))) AND
t1.client_id = ((E'1')))
ORDER BY UPPER(t1.user_id), login_detail0.client_id"
File: .\src\backend\parser\parse_relation.c
Line: 2478
Source: Npgsql
StackTrace:
at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext() in C:\\projects\\Npgsql2\\src\\Npgsql\\NpgsqlState.cs:line 842
at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() in C:\\projects\\Npgsql2\\src\\Npgsql\\NpgsqlDataReader.cs:line 1165
at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() in C:\\projects\\Npgsql2\\src\\Npgsql\\NpgsqlDataReader.cs:line 1181
at Npgsql.ForwardsOnlyDataReader.NextResult() in C:\\projects\\Npgsql2\\src\\Npgsql\\NpgsqlDataReader.cs:line 1367
at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError) in C:\\projects\\Npgsql2\\src\\Npgsql\\NpgsqlDataReader.cs:line 1030
at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) in C:\\projects\\Npgsql2\\src\\Npgsql\\NpgsqlCommand.cs:line 593
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) in C:\\projects\\Npgsql2\\src\\Npgsql\\NpgsqlCommand.cs:line 574
at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\\projects\\Npgsql2\\src\\Npgsql\\NpgsqlCommand.cs:line 521
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at ...()
at ..(IUnitOfWork , IDbCommand , )
at ..(IUnitOfWork , IDbCommand )
at ..(Query )
at Mindscape.LightSpeed.UnitOfWork.Project(Query query)
at Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType)
at Mindscape.LightSpeed.Linq.Plan.GroupResultsPlan.GetGroupsOneAtATime(IUnitOfWork unitOfWork, IList groupingKeys, IList interimKeys, List`1 groupingOn, GroupResultsTypeHandler handler)
at Mindscape.LightSpeed.Linq.Plan.GroupResultsPlan.FetchResultSetForMultipleGroupingKeys(IUnitOfWork unitOfWork, IList groupingKeys, IList interimKeys, GroupResultsTypeHandler handler)
at Mindscape.LightSpeed.Linq.Plan.GroupResultsPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression)
at Mindscape.LightSpeed.Linq.LinqQuery`1.GetEnumerator()
at Infofinder.Statistics.WebService.Service.GetSearches(String fromDate, String toDate, String companyId) in C:\\_SVN\\NxGen\\trunk\\src\\Infofinder.Statistics.WebService\\Service.svc.cs:line 1456
at Infofinder.Statistics.WebService.Service.GetSearchesAsXml(String fromDate, String toDate, String companyId) in C:\\_SVN\\NxGen\\trunk\\src\\Infofinder.Statistics.WebService\\Service.svc.cs:line 238
at SyncInvokeGetSearchesAsXml(Object , Object[] , Object[] )
at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)"
Thanks for your help... Alan
|
|
|
Thanks for the extra detail on this Alan, thats quite useful. I will be having a look into this today and will get in touch if we need any more info on this.
Jeremy |
|
|
Hi Alan, We believe we have repro'ed this and have a fix committed for tonights nightly build (20100423) which will correctly use the existing alias when in a joined table scenario. Let us know how you get on with this.
Jeremy |
|