Hi,

I have a problem, I can't find a solution for. I've searched the forums, but I couldn't find a topic that matches my problem.

I'm using LLBLGen 1.0.2005.1 (latest builds of designer and runtimes), .NET 2.0 and VS 2005.

For explaining the problem I simplified this example:

I have two tables: "Cars" and "EnginesTypes". The table "EngineTypes" has a field called "CarID". I create two entities for those tables and a 1:n relation between "Cars" and "EngineTypes".

Now I want to fetch an EntityCollection. I want the fetch all cars, but for the cars with field "CarType" set to "SportsCar" I only want "EngineTypes" with field "Size" > 2.

LLBLGen will execute two queries. One on table "Cars" and one on table "EngineTypes". In the query for "EngineTypes" I want to add a condition on the field "CarType" in the table "Cars" which should compare the "CarType" of the "Car" which is being referred to by the "CarID".

This would be the SQL statement if it were done in one query with a join:

SELECT * FROM Cars LEFT JOIN EngineTypes ON Cars.ID = EngineTypes.CarID WHERE NOT Cars.CarType = "SportsCar" OR (EngineTypes.Size > 2 OR EngineTypes.Size IS NULL)

The last check on Size IS NULL is to make sure cars without EngineTypes are also selected.

When the EngineTypes are selected separately, the SQL would be something like this:

SELECT * FROM EngineTypes WHERE CarID = @CarID AND (NOT @CarType = "SportsCar" OR Size > 2)

I create an EntityCollection and a new RelationPredicateBucket. I want to add a PredicateExpression with on the field "CarType". But when I try to fetch it, I get an exception:

An exception was caught during the execution of a retrieval query: The column prefix 'dbo.Cars' does not match with a table name or alias name used in the query.

This is happening, because it is making an selection on EngineTypes and it should add a variable to the query, instead of an field-reference on a table that was queried before.

Anyone has a solution??

Thanks, René

I forgot to mention, I use a prefetchpath. I added the relation from Cars to EngineTypes. I want to fetch all cars, with their enginetypes. But for sportcars I only want 2 liter engines or bigger.

LLBLGen produces a query which looks like this (simplified):

SELECT * FROM EngineTypes WHERE CarID = @CarID AND (NOT Cars.CarType = "SportsCar" OR EngineTypes.Size > 2)

Cars.CarType is the problem. It shoudl be something like @CarType, which should be passed by the query-engine.

René

I should also add that I used the filter when adding the relation to the prefetchpath. I didn't use it when I invoke the actual FetchEntityCollection, because I want the restriction on the relation. I also want to fetch the cars which do not have any EngineTypes.

René

Could you paste some code you're using which fails?

Hi Frans,

I'm using a not-too-elegant work-around now. I just deleted the predicate. I basically fetch too much data. In the code where the data is used I select only the info I want. But I still would like to solve this with LLBLGen, because in some cases I fetch way too much data, which I have to filter afterwards.

The code of my example would look like this:

EntityCollection WantedCars = new EntityCollection(new CarsEntityFactory());
DataAccessAdapter adapter = new DataAccessAdapter("Persist Security Info=True;Initial Catalog=Test;Integrated Security=SSPI;Data Source=RL01\\SQLSERVER2000", true, CatalogNameUsage.Clear, "");
PrefetchPath2 prefetchPath = new PrefetchPath2((int)EntityType.CarsEntity);
prefetchPath.Add(CarsEntity.PrefetchPathEngineTypes);
IRelationPredicateBucket filterRelation = new RelationPredicateBucket();

// Here is the problem that I can't add an expression on a field of entity Cars.
// LLBLGen will look for the "CarType"-field in table EngineType, instead of table Cars.
filterRelation.PredicateExpression.Add((CarsFields.CarType != "SportsCar") | (EngineTypesFields.Size >= 2.0));

adapter.FetchEntityCollection(WantedCars, filterRelation, 0, null, prefetchPath);

This is the exception:

"An exception was caught during the execution of a retrieval query: The column prefix 'dbo.EngineTypes' does not match with a table name or alias name used in the query.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

This was the query, which failed:

exec sp_executesql N'SELECT [dbo].[Cars].[CarID] AS [CarId], [dbo].[Cars].[Brand], [dbo].[Cars].[CarType] FROM [dbo].[Cars]  WHERE ( ( ( [dbo].[Cars].[CarType] <> @CarType1 OR [dbo].[EngineTypes].[Size] >= @Size2)))', N'@CarType1 nvarchar(50),@Size2 float', @CarType1 = N'SportsCar', @Size2 = 2.000000000000000e+000

I would appreciate if you have a solution for this.

René

I may suggest another work around, try to use 2 fetches as follows:

1- Fetch an EntityCollection of all cars that don't have a type of "SportsCar" with prefetchPath for the EngineTypes associated with them

SELECT * FROM Cars WHERE Cars.CarType <> "SportsCar"

2- Then Fetch into the same EntityCollection (this will append entities into the same collection) all cars with type of SportsCar using a prefetchPath to EngineTypes and set a filter in the prefetchPath for "Size" > 2.

SELECT * FROM Cars WHERE Cars.CarType = "SportsCar"

Ok, I really wanted to fetch the data in one round-trip to the datalayer. But that's not a big problem. I will try your solution and test if it's better, performance-wise. Thank you for your suggestion.

René