DataAccessAdapter does not contain GetFieldPersistenceInfos and no accessible extension method -error

Posts   
 
    
OKP
User
Posts: 12
Joined: 03-Mar-2022
# Posted on: 03-Mar-2022 13:11:07   

Hi,

I upgraded my llblgen version from v5.2 to v5.9.1 (latest). And at the same time migrated my entities.csproj and entitiesdbspecific.csproj files from .net4.6 to .netstandard2.0. Then I regenerated entities targeting .netstandard2.0. Now in the entitiesdbspecific -project in the DataAccessAdapter -class I get 6 errors when compiling. All the code in this class is autogenerated by llblgen. All the errors indicate missing reference to System.Data. Here's the errors :

Severity    Code    Description 
Error       CS0246  The type or namespace name 'DataTable' could not be found (are you missing a using directive or an assembly reference?) 
Error       CS0103  The name 'ConnectionState' does not exist in the current context    
Error       CS0103  The name 'ConnectionState' does not exist in the current context    
Error       CS0103  The name 'ConnectionState' does not exist in the current context    
Error       CS1061  'DataAccessAdapter' does not contain a definition for 'GetFieldPersistenceInfos' and no accessible extension method 
'GetFieldPersistenceInfos' accepting a first argument of type 'DataAccessAdapter' 
could be found (are you missing a using directive or an assembly reference?)    OK4.EntitiesDBSpecific  

Generated using is not referencing System.Data anymore, only these (see code snippet below). In the version 5.2 it generated a reference also to System.Data

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.DQE.SqlServer;

Why llblgen suddenly is not referencing System.Data when generating entities? What setting am I missing here? This was not happening with version 5.2 and Framework4.6

Best Regards, OKP

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 04-Mar-2022 10:06:07   

In v5.4, as documented, we moved the location of the csproj files for adapter. The csproj files are now in a different folder and your sln referencing these files has to reference the right csproj file, the ones from the new locations. Also make sure you reference the new runtime library assemblies, namely the ones for 5.9.1

Frans Bouma | Lead developer LLBLGen Pro
OKP
User
Posts: 12
Joined: 03-Mar-2022
# Posted on: 06-Mar-2022 14:10:48   

I have done the version 5.4 breaking changes regarding project files and now referencing the latest SD.LLGen.Pro.ORMSupportClasses (v5.9) from nuget. So I believe I have these things correct.

When DataAccessAdapter is generated this is the auto-generated result (see code below): Why is it that in the method ExecuteMultiRowDataTableRetrievalQuery() one parameter is DataTable tableToFill, but no reference to library it resides is generated? In other words no "using System.Data;" ?

//////////////////////////////////////////////////////////////
// <auto-generated>This code was generated by LLBLGen Pro 5.9.</auto-generated>
//////////////////////////////////////////////////////////////
// Code is generated on: 
// Code is generated using templates: SD.TemplateBindings.SharedTemplates
// Templates vendor: Solutions Design.
//////////////////////////////////////////////////////////////
using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.DQE.SqlServer;

namespace OK4.Entities.DatabaseSpecific
{   
    // __LLBLGENPRO_USER_CODE_REGION_START AdditionalNamespaces
    using System.Collections.Generic;
    // __LLBLGENPRO_USER_CODE_REGION_END
    /// <summary>Data access adapter class, which controls the complete database interaction with the database for all objects.</summary>
    /// <remarks>Use a DataAccessAdapter object solely per thread, and per connection. A DataAccessAdapter object contains 1 active connection 
    /// and no thread-access scheduling code. This means that you need to create a new DataAccessAdapter object if you want to utilize
    /// in another thread a new connection and a new transaction or want to open a new connection.</remarks>
    public partial class DataAccessAdapter : DataAccessAdapterBase
    {
        /// <summary>The name of the key in the *.config file of the executing application which contains the connection string.</summary>
        /// <remarks>Default: the value set in the LLBLGen Pro project properties</remarks>
        public static string ConnectionStringKeyName="Main.ConnectionString";

        /// <summary>CTor</summary>
        public DataAccessAdapter() : this(ReadConnectionStringFromConfig(), false, null, null) { }

        /// <summary>CTor</summary>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        public DataAccessAdapter(bool keepConnectionOpen) : this(ReadConnectionStringFromConfig(), keepConnectionOpen, null, null) { }

        /// <summary>CTor</summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        public DataAccessAdapter(string connectionString) : this(connectionString, false, null, null) { }

        /// <summary>CTor</summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        public DataAccessAdapter(string connectionString, bool keepConnectionOpen) : this(connectionString, keepConnectionOpen, null, null) { }
        
        /// <summary>CTor.</summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        /// <param name="catalogNameUsageSetting"> Configures this data access adapter object how to threat catalog names in persistence information.</param>
        /// <param name="catalogNameToUse"> The name to use if catalogNameUsageSetting is set to ForceName. Ignored otherwise.</param>
        /// <remarks>For backwards compatibility.</remarks>
        public DataAccessAdapter(string connectionString, bool keepConnectionOpen, CatalogNameUsage catalogNameUsageSetting, string catalogNameToUse) 
                : base(PersistenceInfoProviderSingleton.GetInstance())
        {
            InitClassPhase2(connectionString, keepConnectionOpen, catalogNameUsageSetting, SchemaNameUsage.Default, catalogNameToUse, string.Empty, null, null);
        }

        /// <summary>CTor</summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        /// <param name="schemaNameUsageSetting">Configures this data access adapter object how to threat schema names in persistence information.</param>
        /// <param name="schemaNameToUse">Oracle specific. The name to use if schemaNameUsageSetting is set to ForceName. Ignored otherwise.</param>
        public DataAccessAdapter(string connectionString, bool keepConnectionOpen, SchemaNameUsage schemaNameUsageSetting, string schemaNameToUse) 
                : base(PersistenceInfoProviderSingleton.GetInstance())
        {
            InitClassPhase2(connectionString, keepConnectionOpen, CatalogNameUsage.Default, schemaNameUsageSetting, string.Empty, schemaNameToUse, null, null);
        }

        /// <summary>CTor.</summary>
        /// <param name="connectionString">The connection string to use when connecting to the database.</param>
        /// <param name="keepConnectionOpen">when true, the DataAccessAdapter will not close an opened connection. Use this for multi action usage.</param>
        /// <param name="catalogNameOverwrites"> The from-to name value pairs and setting for the overwriting of catalog names. Can be null.</param>
        /// <param name="schemaNameOverwrites"> The from-to name value pairs and setting for the overwriting of schema names. Can be null.</param>
        public DataAccessAdapter(string connectionString, bool keepConnectionOpen, CatalogNameOverwriteHashtable catalogNameOverwrites, SchemaNameOverwriteHashtable schemaNameOverwrites) 
                : base(PersistenceInfoProviderSingleton.GetInstance())
        {
            InitClassPhase2(connectionString, keepConnectionOpen, CatalogNameUsage.Default, SchemaNameUsage.Default, string.Empty, string.Empty, catalogNameOverwrites, schemaNameOverwrites);
        }

        /// <summary>Sets the flag to signal the SqlServer DQE to generate SET ARITHABORT ON statements prior to INSERT, DELETE and UPDATE Queries.
        /// Keep this flag to false in normal usage, but set it to true if you need to write into a table which is part of an indexed view.
        /// It will not affect normal inserts/updates that much, leaving it on is not harmful. See Books online for details on SET ARITHABORT ON.
        /// After each statement the setting is turned off if it has been turned on prior to that statement.</summary>
        /// <remarks>Setting this flag is a global change.</remarks>
        public static void SetArithAbortFlag(bool value)
        {
            DynamicQueryEngine.ArithAbortOn = value;
        }

        /// <summary>Sets the default compatibility level used by the DQE. Default is SqlServer2005. This is a global setting.
        /// Compatibility level influences the query generated for paging, sequence name (@@IDENTITY/SCOPE_IDENTITY()), and usage of newsequenceid() in inserts. 
        /// It also influences the ado.net provider to use. This way you can switch between SqlServer server client 'SqlClient' and SqlServer CE Desktop.</summary>
        /// <remarks>Setting this property will overrule a similar setting in the .config file. Don't set this property when queries are executed as
        /// it might switch factories for ADO.NET elements which could result in undefined behavior so set this property at startup of your application</remarks>
        public static void SetSqlServerCompatibilityLevel(SqlServerCompatibilityLevel compatibilityLevel)
        {
            DynamicQueryEngine.DefaultCompatibilityLevel = compatibilityLevel;
        }

        /// <summary>Creates a new Dynamic Query engine object and passes in the defined catalog/schema overwrite hashtables.</summary>
        protected override DynamicQueryEngineBase CreateDynamicQueryEngine()
        {
            return this.PostProcessNewDynamicQueryEngine(new DynamicQueryEngine());
        }

        /// <summary>Reads the value of the setting with the key ConnectionStringKeyName from the *.config file and stores that value as the active connection string to use for this object.</summary>
        /// <returns>connection string read</returns>
        private static string ReadConnectionStringFromConfig()
        {
#if NETSTANDARD || NETCOREAPP
            return RuntimeConfiguration.GetConnectionString(ConnectionStringKeyName);
#else
            return ConfigFileHelper.ReadConnectionStringFromConfig(ConnectionStringKeyName);
#endif
        }
        
        /// <summary>Sets the per instance compatibility level on the dqe instance specified.</summary>
        /// <param name="dqe">The dqe.</param>
        protected override void SetPerInstanceCompatibilityLevel(DynamicQueryEngineBase dqe)
        {
            if(_compatibilityLevel.HasValue)
            {
                ((DynamicQueryEngine)dqe).CompatibilityLevel = _compatibilityLevel.Value;
            }
        }

        private Nullable<SqlServerCompatibilityLevel> _compatibilityLevel = null;
        
        /// <summary>The per-instance compatibility level used by this DQE instance. Default is the one set globally, which is by default SqlServer2005 (for 2005+). 
        /// Compatibility level influences the query generated for paging, sequence name (@@IDENTITY/SCOPE_IDENTITY()), and usage of newsequenceid() in inserts. 
        /// It also influences the ado.net provider to use. This way you can switch between SqlServer server client 'SqlClient' and SqlServer CE Desktop.</summary>
        public Nullable<SqlServerCompatibilityLevel> CompatibilityLevel
        {
            get { return _compatibilityLevel; }
            set { _compatibilityLevel = value; }
        }


        // __LLBLGENPRO_USER_CODE_REGION_START CustomDataAccessAdapterCode

        public static Dictionary<Guid, DbCommand> s_DbCommands = new Dictionary<Guid, DbCommand>();

        public Guid? CancellationId { get; set; }

        public static object s_LockObj = new object();

        public override bool ExecuteMultiRowDataTableRetrievalQuery(IRetrievalQuery queryToExecute, DbDataAdapter dataAdapterToUse, DataTable tableToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo)
        {
            if (CancellationId.HasValue)
            {
                lock (s_LockObj)
                {
                    s_DbCommands[CancellationId.Value] = queryToExecute.Command;
                }
            }
            return base.ExecuteMultiRowDataTableRetrievalQuery(queryToExecute, dataAdapterToUse, tableToFill, fieldsPersistenceInfo);
        }

        public static void CancelDBCommand(Guid cancellationId)
        {
            lock (s_LockObj)
            {
                DbCommand dbCommand;
                if (s_DbCommands.TryGetValue(cancellationId, out dbCommand))
                {
                        //Connection.State produces compiler error because reference to System.Data is missing.
                    if (dbCommand.Connection.State == ConnectionState.Open ||
                        dbCommand.Connection.State == ConnectionState.Executing ||
                        dbCommand.Connection.State == ConnectionState.Fetching)
                    {
                        dbCommand.Cancel();
                        
                    }
                }
            }
        }

        public static void RemoveDBCommand(Guid cancelId)
        {
            lock (s_LockObj)
            {
                s_DbCommands.Remove(cancelId);
            }
        }

        public string GetTableNameFromEntity(IEntity2 entity)
        {
            return this.GetFieldPersistenceInfos(entity)[0].SourceObjectName;   //This method is not found => compiler error
        }

        // __LLBLGENPRO_USER_CODE_REGION_END

    }
}

This was generated when using v5.3


//////////////////////////////////////////////////////////////
// <auto-generated>This code was generated by LLBLGen Pro 5.3.</auto-generated>
//////////////////////////////////////////////////////////////
// Code is generated on: 
// Code is generated using templates: SD.TemplateBindings.SharedTemplates
// Templates vendor: Solutions Design.
// Templates version: 
//////////////////////////////////////////////////////////////
using System;
using System.Collections;
using System.Data;   //It exists => no Errors.
using System.Data.Common;
using System.Configuration;
using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.DQE.SqlServer;


OKP
User
Posts: 12
Joined: 03-Mar-2022
# Posted on: 07-Mar-2022 09:13:14   

Hi,

I noticed that the error producing code was in the user code region. Ie. manually added into the file at some point in project history and never touched since. I wasn't aware of this feature and misthought that the whole code was something the new llblgen version generated differently.
But in the end it was not an llblgen issue. I managed to solve the problems I had by modifying this user block accordingly. Sorry for the inconvenience and thanks for the replies, this thread can be considered resolved.

Best Regards, OKP

DaveH2022
User
Posts: 5
Joined: 15-Sep-2022
# Posted on: 15-Sep-2022 21:34:38   

Hi,

I'm not sure I'm having the same issue as above, but it's the closest report I found. We're on LLBLGen Pro v4.2, and finally jumping to v5.9.2. Initially I had many errors (all ours) but have been able to resolve almost everything. Now building our solution reports only one problem (in two locations)...

Our Solution includes several different projects, including our Data.AdapterDBSpecific and Data.AdapterGeneric projects where LLBLGen Pro generates its code into.

We have several custom templates, including an InsertFromQueryDataAccessAdapter.template that generates an InsertFromQueryDataAccessAdapter.cs file into our Data.AdapterDBSpecific project. In that code we have a method that includes these two statements:

IFieldPersistenceInfo[] selectPersistenceInfo = GetFieldPersistenceInfos(selectFields);

and later:

IFieldPersistenceInfo[] selectFieldPersistenceInfos = GetFieldPersistenceInfos(insertIntoTable);

That file includes:

using SD.LLBLGen.Pro.ORMSupportClasses;
using SD.LLBLGen.Pro.DQE.SqlServer;

(though DQE.SqlServer is grayed out/not needed)

THE ISSUE: Visual Studio 2019 is reporting "The name 'GetFieldPersistenceInfos' does not exist in the current context". Hovering over "GetFieldPersistenceInfos" in the code to "Show Potential Fixes" only suggests generating a new method (not suggesting to add a "using" etc.).

If I temporarily change the offending statements (just replacing GetFieldPersistenceInfos(x) with null) then our entire solution builds without error, which I think confirms everything is valid/proper except no longer being able to find the GetFieldPersistenceInfos(x) method.

I suspect/hope the solution will be painfully obvious to you, but I'm currently stuck. I've checked:

  • Our projects reference SD.LLBLGen.Pro.DQE.SqlServer and/or SD.LLBLGen.Pro.ORMSupportClasses. I confirmed that their Path is pointing to a copy of the DLLs that were provided in v5.9 (we copy those DLLs into a "lib" folder in one of our projects, so that all our other projects can reference that central location.)
  • Inspected our Data.AdapterDBSpecific and Data.AdapterGeneric projects -- I clicked VS's "show all files" icon and some hidden folders appear, but I didn't see any newly-generated .cs files being omitted from either project (though I didn't really expect to find a source file that defines GetFieldPersistenceInfos...I think it's in the ORMSupportClasses.dll?)
  • From the migration page and the original post in this thread, there's mention of files being moved, and it wasn't super clear, but it seemed to say to check the Solution to ensure it's referencing the new project file locations. I did confirm our bigger Solution is referencing our Data.AdapterDBSpecific and Data.AdapterGeneric projects. (And again, temporarily replacing GetFieldPersistenceInfos(x) with null fully builds with no errors.)
  • I wondered if I was missing a using, or needed to fully-qualify GetFieldPersistenceInfos, etc., but googling didn't uncover any examples/other suggestions.

Thanks for your help!

--Dave

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 16-Sep-2022 09:13:02   

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 simple_smile

(and please next time start a new thread, thanks simple_smile )

Frans Bouma | Lead developer LLBLGen Pro
DaveH2022
User
Posts: 5
Joined: 15-Sep-2022
# Posted on: 17-Sep-2022 02:29:01   

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 simple_smile

I noted there were some bulk insert enhancements, but I hadn't researched the details yet...but since you asked... simple_smile

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));
}
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 17-Sep-2022 09:19:32   

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 simple_smile

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.

Frans Bouma | Lead developer LLBLGen Pro