Hi,

I'm trying to convert code that uses the IN clause for ids to a Temp Table solution. I have created everything for creating the table and inserting the data into the temp table. So far so good.

Now i try to make an inner join to the temp table, but can't quite figure out how.

I have tried to create an entity field named "id" and the table has a default name of "#TempTable" and use that in a EntityRelation

here are the pieces of code i have tried to use

public const string TableName = "#TempTable";
public const string KeyName = "id";

public IEntityField2 PrimaryKey = new EntityField2(new FieldInfo(KeyName, TableName, typeof(int), true, false, true, false, 0, Int32.MaxValue, 1, 1));

public IEntityRelation CreateRelation(EntityField2 joinField)
{
     return new EntityRelation(this.PrimaryKey, joinField, RelationType.OneToMany);
}

And this is the code for creating the query

var bucket = new RelationPredicateBucket();

var tempTable = new TempTable(incidentIds);
bucket.Relations.Add(tempTable.CreateRelation(IncidentFields.IncidentId));
using (var adapter = AdapterManager.CreateAdapter())
{
      tempTable.TransfereData(adapter);

      var incidents = new EntityCollection<IncidentEntity>();
      adapter.FetchEntityCollection(incidents, bucket, prefetchPath);
      return IncidentDto.ToDtoList(incidents);
}

When i try to add the relation to a bucket i get the following error at execution

The element name '#TempTable' isn't known in this provider

It is clear why it gives me the error, but is there a way to emit some literal text for the relation? or any other suggestions

I'm trying to convert code that uses the IN clause for ids to a Temp Table solution. I have created everything for creating the table and inserting the data into the temp table. So far so good.

Are these Ids retrieved from another entity? I'm asking this because you can use a Subquery instead of the IN clause.

Walaa wrote:

I'm trying to convert code that uses the IN clause for ids to a Temp Table solution. I have created everything for creating the table and inserting the data into the temp table. So far so good.

Are these Ids retrieved from another entity? I'm asking this because you can use a Subquery instead of the IN clause.

No these ids are originating from an client application.

There is another way if you want to consider it, instead of trying to produce raw sql.

Instead of creating TempTables over and over for this operation to perform a join. You can create a fixed table with 2 columns, IDs & Token (GUID).

So you can use LLBLGen to map this table into an entity, and so you can use LLBLGen to insert into this table, rather than using conventional ways for insertion. And so it would be easy to join to this entity, and delete those inserted rows using the Token for filtering.

Although having a table/entity which doesn't persist data might seem odd to some, I do believe it comes handy in such cases instead of temptables with an ORM.

That is an possible solution, but it will an cause extra query afterwards to clear the table for a specific token. While an TempTable will automatically be cleared when the session ends (connection closes).

So would there be a way to produce a join to a table that does not exist in LLBLGen? Maybe a custom implementation of IEntityRelation?

You can't directly add a temp table mapping. However it's not impossible

As you use adapter, there's a way, but it does require some extra code. In a partial class of DataAccessAdapter you have to override the GetFieldPersistenceInfo and GetFieldPersistenceInfos methods. They produce IFieldPersistenceInfo objects for the passed in elements. They by default return static objects which represent the target elements, but you can, by overriding these elements, return objects which instead target your temp table.

You have to return instances of FieldPersistenceInfo, which is a class in the ORMSupportClasses.

So you have to figure out how to determine when to return these mappings and when to return the default ones, based on the input. You could opt for using derived class of DataAccessAdapter instead and override the methods there and only use this derived class when you deal with the temp table.

Thank you Otis,

I have created a Temp Entity and integrated that with the adapter to fill the temp table at the right moment. Also let the adapter return the correct FieldInfo so the rest of the engine was satisfied.