3.1.11.706 Final (SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll) 3.1.12.0222 (SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll 3.1.12.216 (SD.LLBLGen.Pro.LinqSupportClasses.NET35) DotNet 4.0 vs2010 project Adapter template

When LLB creates the EXISTS (SELECT ...) for .Any() it uses what is apparently a random field name. It will pick from all the fields in the entity which means any .IncludeFields in the parent sql gets ignored. Here is some SQL that crashes as 'RepealNotes' doesn't exist in the database revision being used:

exec sp_executesql N'SELECT [LPA_L1].[RegID], [LPA_L1].[LanguageCode], [LPA_L1].[RegTitle], [LPA_L1].[RegSummary], [LPA_L1].[ConsolidatedHyperlink] FROM [enhesaSQL_20120305_1].[dbo].[RegulationText]  [LPA_L1]   WHERE ( ( ( ( ( ( ( ( NOT  EXISTS (SELECT [LPLA_2].[RepealNote] FROM [enhesaSQL_20120305_1].[dbo].[RegulationText]  [LPLA_2]   WHERE ( ( ( [LPLA_2].[LanguageCode] = @p1) AND ( [LPLA_2].[RegID] = [LPA_L1].[RegID])))) AND ( [LPA_L1].[LanguageCode] = @p2)) OR ( [LPA_L1].[LanguageCode] = @p3)) AND ( [LPA_L1].[RegID] = @p4)))))))',N'@p1 varchar(6),@p2 varchar(6),@p3 varchar(6),@p4 int',@p1='en',@p2='en',@p3='en',@p4=286

The '[LPLA_2].[RepealNote]' could just as easily be NULL and would avoid the problem. is there a workaround for this? Note that the top level sql has only included relevant fields.

Could you please post the code that reproduces such query?

daelmo wrote:

Could you please post the code that reproduces such query?

OK. I need to simplify the Linq a lot and the sql is different but still shows the same issue

This Linq:

bool queryable = metaData.RegulationText.Any(a=>a.Regulation.BaseLanguageCode == a.LanguageCode);

produces this SQL:

Query: SELECT TOP(@p2) CASE WHEN  EXISTS (
     SELECT [LPA_L3].[RepealNote] FROM ( [dbo].[Regulation] [LPA_L2]  INNER JOIN [dbo].[RegulationText] [LPA_L3]  ON  [LPA_L2].[RegID]=[LPA_L3].[RegID]
)
WHERE ( ( [LPA_L2].[BaseLanguageCode] = [LPA_L3].[LanguageCode]))) THEN 1 ELSE 0 END AS [LPFA_1] FROM [dbo].[RegulationText]  [LPLA_1]  
Parameter: @p2 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

I need to be able to get the sql to use NULL or the primary key instead of picking a random column to insulate this from additional columns in newer schema being used.

i.e. RepealNote exists in the latest schema, but not in the published database that is being used for a specific client.

I see . IMHO you shouldn't use that version of the generated code in a client that connects to a different version of the DB schema: you will have other problems, like in a simple collection fetch where all the fields are part of the SELECT statement.

So, in my opinion you should use a version of the generated code that match with the DB schema from which it was created. Anyway I will check whether this change might be possible.

I will see if using revisions from the svn repo can help me do that. Thanks.

It's difficult to refactor this, how silly it might sound, as the pipeline to create the exists subquery relies on a field being present (as it's also used for other subquery fragments, as it's the same predicate). Databases ignore any field in an EXISTS query, hence we simply emit it. I'm with David on this, you should really keep the schemas in sync with the code working on it, you'll run into errors which might hard to track down sooner or later.

I have set in motion a procedure to ensure that applications we create that use old databases freeze the LLB dlls (in vn:externals) to the same revision. svn 1.7.6 makes this simple.

If we create new functionality that requires anything in the new schema, we will back-sync all the databases in current use to be the same schema. This is hard sometimes when data constraints prevent it, but I guess we need to massage the data in those cases.

Just for background, our applications use 2 databases: a live one for current data such as client contracts and a snapshot for the data as a the date the client needs to see it. Sometimes we need to deliver data that is several months to a year old (for compliance auditing as at a certain data). As well as potentially having slightly different schema we use catalog rewriting to set the catalog dynamically according to the client contract.

This will change once our hysterical (whoops! historical:-) database is deployed. But that's a whole new story.

Thanks to you both.