DataAccessAdapter does not contain GetFieldPersistenceInfos and no accessible extension method -error
Joined: 03-Mar-2022
Joined: 03-Mar-2022
Joined: 03-Mar-2022
Joined: 15-Sep-2022
The methods you're looking for were factored into a new class in v5.8. See https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/migratingcode.htm#breaking-changes-v5.8 which also discusses how to get to the methods (as they're protected internal). So you have to derive a class from QueryCreationManager, and add methods to that class which you can call in your generated code and which call the base class' GetFieldPersistenceInfo etc.
Out of curiosity, what does this InsertFromQueryDataAccessAdapter do as in general users don't need to access GetFieldPersistenceInfo etc. Perhaps there's a better way nowadays to do what you want to do 
(and please next time start a new thread, thanks )
Joined: 15-Sep-2022
Thanks for the v5.8 pointer (of course I found that about an hour after posting, but decided to wait for confirmation before trying it)...and next time I'll start a new thread.
Otis wrote:
Out of curiosity, what does this InsertFromQueryDataAccessAdapter do as in general users don't need to access GetFieldPersistenceInfo etc. Perhaps there's a better way nowadays to do what you want to do
I noted there were some bulk insert enhancements, but I hadn't researched the details yet...but since you asked...
I didn't write the original code. Although I understand its purpose and the resulting SQL, I'll admit that I haven't yet figured out exactly how it works, or why GetFieldPersistenceInfo is needed. I will copy the code below FYI, and will describe our use case here (with some simplification) in case you have alternate suggestions/approaches we should consider.
- We have UserAccount records with a variety of attributes (e.g., job role, work location, etc.).
- We have a UI where an admin can define an "Audience" by specifying various desired filtering criteria (e.g., "include everyone in Job X, Y, or Z...exclude Locations A, B").
- We have an AudienceMember join table where, for a specific AudienceId, we record all UserAccountIds of UserAccounts who belong in that Audience. (Currently its only columns are AudienceId and UserAccountId.)
- The companies we support "constantly" add new employees, de-activate existing employees, and change existing employee details (e.g. move an employee to a new job role; employee moves and changes work location, etc.).
- Therefore we have a "refresh audience" process that finds UserAccounts that are currently meet the criteria to be in the audience, then adjusts the AudienceMember join table to insert UserAccountsIds that are missing, and delete UserAccountsIds that no longer belong. The result gets our AudienceMember join table back "in sync" with the current UserAccount data.
A "simple" approach to this refresh would be to fetch the list of UserAccountIds who currently belong in the audience, then fetch all UserAccountIds from the AudienceMember join table (for the specific audience), and then compare those lists to execute appropriate "deletes" or "inserts" to get the AudienceMember join table in sync. However, that would result in a lot of network traffic with potentially large payloads. (And the code would probably need to process in smaller batches to avoid the "too many SQL parameters" exception).
Originally we might've even considered only fetching the current list of UserAccountIds into memory, then JSON-serializing that list to store in a text column (via a single UpdateEntitiesDirectly call)...BUT there are reasons that we need the formal AudienceMember join table (e.g. reporting; having other processes that regularly join their own tables to the current AudienceMember table to see which of their records need adjustment; and so on).
Therefore OUR CURRENT APPROACH is to start with with an LLBL PredicateBucket with the criteria that will "find all UserAccounts that currently belong in a specific Audience." That filter is a combination of (some "base" criteria that we impose) plus (any additional criteria specified/selected by the organization's administrator). Next we...
- Use LLBL's DeleteEntitiesDirectly to execute a single SQL statement to "delete from AudienceMember where (AudienceId==X) AND (UserAccountId NOT in {current Audience})"
- Use our InsertIntoTableDirectly method to execute a single SQL statement to "insert into AudienceMember (select @audienceId, UserAccount.Id (from UserAccount where [user account is in {current Audience}] AND UserAccount.Id NOT in [AudienceMember where AudienceId==@audienceId]))"
So in 2 statements we have the database perform all the deletes, then all the inserts, which gets our AudienceMember join table updated to reality again.
Please let me know if LLBL now has a "native" way to do this, or if you have other suggestions/approaches to consider.
Thanks!
--Dave
/// <summary>
/// Generate and execute an INSERT INTO x SELECT FROM y statement.
/// </summary>
/// <param name="selectFields">The fields being selected.</param>
/// <param name="selectFilter">A bucket that will be applied to the fields being selected.</param>
/// <typeparam name="T">The table type being inserted into.</typeparam>
/// <returns>execution result, which is the amount of rows affected (0-N)</returns>
public int InsertIntoTableDirectly<T>(IEntityFields2 selectFields, IRelationPredicateBucket selectFilter)
where T : IEntity2, new()
{
var x = new SD.LLBLGen.Pro.ORMSupportClasses.Adapter.QueryCreationManager();
IEntity2 insertIntoTable = new T();
IFieldPersistenceInfo[] selectPersistenceInfo = GetFieldPersistenceInfos(selectFields);
var queryParameters = new QueryParameters
{
FieldsForQuery = selectFields,
FieldPersistenceInfosForQuery = selectPersistenceInfo,
FilterToUse = selectFilter.PredicateExpression,
RelationsToUse = selectFilter.Relations,
AllowDuplicates = false,
};
IRetrievalQuery selectQuery = CreateQueryFromElements(this, queryParameters.Clone());
// Since the DynamicQueryEnginer doesn't support INSERT INTO SELECT we build the query directly here
IDbSpecificCreator creator = CreateDynamicQueryEngine().Creator;
IFieldPersistenceInfo[] selectFieldPersistenceInfos = GetFieldPersistenceInfos(insertIntoTable);
var queryText = new StringBuilder(DynamicQueryEngineBase.InsertQueryBufferLength);
queryText.AppendFormat("INSERT INTO {0} (", creator.CreateObjectName(selectFieldPersistenceInfos[0]));
for (var i = 0; i < insertIntoTable.Fields.Count; i++)
{
queryText.AppendFormat("{0}, ", creator.CreateFieldNameSimple(selectFieldPersistenceInfos[insertIntoTable.Fields[i].FieldIndex], insertIntoTable.Fields[i].Name));
}
queryText.Remove(queryText.Length - 2, 2);
queryText.Append(") ");
queryText.Append(selectQuery.Command.CommandText);
selectQuery.Command.CommandText = queryText.ToString();
return ExecuteActionQuery(new ActionQuery(selectQuery.Connection, selectQuery.Command));
}
LLBLGen Pro can execute SQL statements directly nowadays, https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/gencode_adapter.htm so you can build the SQL query in your own code and run it without overriding any method. The runtime nowadays also supports batching so you e.g. insert 1000 entities and set the batch to 200 and it will build 5 queries and execute them. Batching isn't supported for self-referencing entities. See: https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/gencode_querybatching.htm
Those are tools you can use today to simplify your code. Now, on to your requirement
Have you looked into implementing the AudienceMember as an Indexed View ? (you basically do that manually now). This way, you let SQL Server take care of the maintenance of the set. It'll update the rows when a row used in the view is updated / inserted / deleted. This might give a bit performance loss on these operations for individual rows, but it'll save you a lot of trouble (and it'll be up to date). Indexed Views have a lot of requirements you have to meet to make them work tho, so it might be your situation isn't compatible.
Another way to do this perhaps is to rebuild the AudienceMember set for a given audience when it changes, and when the user or the tables related to user which are used to build AudienceMember are updated. The latter doesn't give a large set of data so you can do that without running into a lot of network traffic (you can even think of implementing this as triggers in the DB Schema).
Diffing the sets of ID's might be a simpler approach too: you obtain the current set of IDs and you obtain the set of IDs that should be in the audience. Use a simple query in-memory using linq's except/intersect and you get 2 sets: one for deleting (the ones which aren't in the audience anymore) and one for inserting (the ones which are currently not in the audience). This will at least save you the trouble of removing rows which you'll insert immediately afterwards.