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
|
Good day,
We have a section of code which generates a LINQ expression from user input (a complex search engine). The following is an example of a LINQ expression built from the search system:
List<Searchlogentry> ListSearchlogentry = null; IQueryable <Searchlogentry> _querysearchlogentry1 = LightSpeedContextHelper.GetQueryable<Searchlogentry>(); IQueryable <Searchlogentry> _querysearchlogentry2 = LightSpeedContextHelper.GetQueryable<Searchlogentry>();
var Query = from selesearchlogentry1 in _querysearchlogentry1 join selesearchlogentry2 in _querysearchlogentry2 on selesearchlogentry1.Id equals selesearchlogentry2.Id where (selesearchlogentry1.Iseffective == true) && selesearchlogentry2.Iseffective == true select selesearchlogentry1; ListSearchlogentry = new List<Searchlogentry>(); foreach (Searchlogentry _lentry in Query) { ListSearchlogentry.Add( _lentry); } return ListSearchlogentry;
- Please note the construction of this particular search involves the table being referenced twice. It is suboptimal of course but I believe it should not fail in the way that it does.
The generated SQL is as follows -NB (truncated column selectors since there are about sixty of them per table listed individually)
SELECT t0.logentryid AS "t0.logentryid", t0.Activelogentryid AS "t0.Activelogentryid", [...], t1.logentryid AS "t1.logentryid", t1.Activelogentryid AS "t1.Activelogentryid", [...], t2.logentryid AS "t2.logentryid", t2.Activelogentryid AS "t2.Activelogentryid", [...] FROM jocwatch.Searchlogentry t0 INNER JOIN jocwatch.Searchlogentry t1 ON t0.logentryid = t1.logentryid WHERE (t0.Iseffective = ((TRUE)) AND t1.Iseffective = ((TRUE)))
As you can see, the SQL references the same table three times yet the LINQ only twice. We then receive an exception (follows) which suggests that the SQL failed because t2 is not joined or referenced properly.
I would be grateful if somebody could shed some light on this. We are running the nightly build (6 Sep 2011) of 3.1x
ERROR: 42P01: missing FROM-clause entry for table "t1"
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Npgsql.NpgsqlException: ERROR: 42P01: missing FROM-clause entry for table "t1"
Source Error:
Line 140: // Do a first check of the input parameters
Line 141:
Line 142: List<Searchlogentry> retVal = (List<Searchlogentry>)LogentrySearch.BuildFromRequest(Request.QueryString);
Line 143:
Line 144:
Source File: C:\Projects\TFS\src\JW.Web\JOCWatch\Report.aspx.cs Line: 142
Stack Trace:
[NpgsqlException (0x80004005): ERROR: 42P01: missing FROM-clause entry for table "t1"]
Npgsql.<ProcessBackendResponses_Ver_3>d__a.MoveNext() +2999
Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() +773
Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() +90
Npgsql.ForwardsOnlyDataReader.NextResult() +68
Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError) +158
Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) +355
Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) +346
Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +9
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() +23
() +24
(IUnitOfWork , IDbCommand ,) +471
(IUnitOfWork , IDbCommand ) +94
Mindscape.LightSpeed.Data.DataProviderAdapter.(IUnitOfWork , IEnumerable`1 , ProviderOptions ) +157
(List`1 , Query , EntityTuple ) +196
(Query , EntityTuple ) +486
Mindscape.LightSpeed.UnitOfWork.FindGroup(Query query, EntityTuple results) +117
Mindscape.LightSpeed.UnitOfWorkBase.Find(Query query, EntityMap typeMap) +119
Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType) +825
Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) +273
Mindscape.LightSpeed.Linq.LinqQuery`1.GetEnumerator() +38
NC3A.QueryBuilder.SearchClass.Search(UnitOfWork UOW) +7292
NC3A.QueryBuilder.SearchClass.InvokeSearch(UnitOfWork UOW) +82
[TargetInvocationException: Exception has been thrown by the target of an invocation.]
System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner) +0
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks) +639
System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture) +38
System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters) +35
NC3A.JocWatch.DataLayer.CodeDomExtender.ReflectionExtensions.Invoke(MethodInfo methodInfo, Object[] parameters) in C:\Projects\TFS\src\JW.Domain\Entities\JOCWatchDataModel\CodeDomExtender\ReflectionExtensions.cs:13
NC3A.JocWatch.DataLayer.Search.BaseLinqSearchCompiler.RunQuery(String CodeBody, JocwatchDataModelUnitOfWork jocDb) in C:\Projects\TFS\src\JW.Domain\Entities\JOCWatchDataModel\NC3A.JocWatch.DataLayer.Utils.cs:1895
NC3A.JocWatch.DataLayer.Search.SearchExpressionBuilder.BuildFromRequest(NameValueCollection parameters) in C:\Projects\TFS\src\JW.Domain\Entities\JOCWatchDataModel\NC3A.JocWatch.DataLayer.Utils.cs:1779
JOIIS.Web.JOCWatch_Report.Page_Load(Object sender, EventArgs e) in C:\Projects\TFS\src\JW.Web\JOCWatch\Report.aspx.cs:142
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +25
System.Web.UI.Control.LoadRecursive() +116
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2750
|
|
|
|
