How can I use the generated metadata to get a COUNT(*) on every entity/table in the project?

(I'm happy to use reflection on PersistenceInfoProviderSingleton if required)

You mean, you want to execute a COUNT(*) on the DB for every entity known in the generated code?

Otis wrote:

You mean, you want to execute a COUNT(*) on the DB for every entity known in the generated code?

That was my immediate goal but the metadata os all hidden away in PersistenceInfoProviderCore!

Anyway, this code addition to DataAccessAdapter works and although it hardcodes the database for now:-

        public DataTable CallSQLDirect(string sql)
        {
            var result = new DataTable();

            using (var command = GetDbProviderFactoryInstance().CreateCommand())
            {
                if (command == null) return result;

                command.CommandType = CommandType.Text;
                command.CommandText = sql;
                command.Connection = GetActiveConnection();

                if (IsTransactionInProgress)
                {
                    command.Transaction = PhysicalTransaction;
                }

                if (CommandTimeOut > 0)
                {
                    command.CommandTimeout = CommandTimeOut;
                }

                using (var physicalDataAdapter = CreateNewPhysicalDataAdapter())
                {
                    physicalDataAdapter.SelectCommand = command;
                    physicalDataAdapter.Fill(result);
                }
            }

            return result;
        }

        public DataTable GetTableRowCounts()
        {
            const string SQL = @"
USE TIPS
CREATE TABLE #RowCount (TableName varchar(128), Count int)
EXEC sp_MSforeachtable 'INSERT INTO #RowCount SELECT PARSENAME(''?'', 1), COUNT(*) FROM ?'
SELECT * from #Rowcount ORDER BY TableName
DROP TABLE #RowCount";

            return CallSQLDirect(SQL);
        }

Thanks for sharing. But I'm curious why do you need this? Just need to understand the business case. In the usual solutions such counts might be needed in some dashboard or something, which can be developed using the generated and known in advance Object Model/Entities.

Walaa wrote:

Thanks for sharing. But I'm curious why do you need this? Just need to understand the business case. In the usual solutions such counts might be needed in some dashboard or something, which can be developed using the generated and known in advance Object Model/Entities.

Spot-on, its for a dashboard. And it helps me debugging to see what row counts changed when I save something for example.

There are 153 tables currently - I'm not going to write code for each entity so that's why I initially wanted to enumerate the entity types using the metadata. However this SQL works fine since this is model-first and tables are 1:1 with entities anyway.

Would still be nice if the metadata wasn't internal though.

As an aside, by rewriting the table name output in the sql and running it directly on the db, I could then easily compare it to that created in PersistenceInfoProviderCore.Init(). It showed that a few tables were not the same as their entity names. It was only minor stuff - plural names for tables with singular names for entities mainly. I thought it would be nice for model-first development to have a Warning that the entity/table names do not match. Just a suggestion.

Couldn't you just call LinqMetaData.GetQueryableForEntity() and passing every value of EntityType to it, and then append .Count() ? That will execute a Count() query for the entity, could be done in a couple of lines, no reflection needed.

Otis wrote:

...passing every value of EntityType to it

I don't understand what this means.

simmotech wrote:

Otis wrote:

...passing every value of EntityType to it

I don't understand what this means.

EntityType is the enum generated, which contains for each entity a value. So you can use the Enum static methods to obtain all values and then pass these one by one to the method I suggested.

Ah, I see, but would that allow me to get the table name as well as the count? I don't use Linq to Entities really.

My bigger point is that the metadata seems a bit locked down and hidden away which is a shame because it can be very useful.

I have just thought of another use case where the metadata would be useful: I have plenty of reference data entities that are simple lookups - say an ID and a Description. I am writing a maintenance form so the user can Add/Remove/Modify those Descriptions. It is a generic form and used for many of these lookup entity types.

I would be helpful to know which of these lookups are currently 'in use'. That way in the GUI, I can disable deletion of those rows rather than the suck-it-and-see method where I attempt to delete the user selection one at a time and ignore exceptions.

A Count of current usage would be even better since I can show the user that information - they may decide that if two Descriptions are nearly the same but one is only used once, they will change that one then get rid of the now unused Description.

Because these lookups can be used in multiple places, I would have to Count their usage on each related entity type. Future addition or removal of relations would then necessitate a code change. Failure to change the code accordingly could mean runtime failures.

That is where the Metadata would come in handy. I should be able to write a helper method that takes an entity type, uses its relational meta data to determine which entities it is related to and sums up the Count of usage as its result. Actually, internally it could fetch the IDs of the entity where the FK matches. I should then be able to write a helper method that says "Replace all occurrences of the PK for Description A with the PK for Description B then delete Description A"

The accounts package I am currently writing could also benefit from this. User wants to delete a used Nominal Account, they just choose another Nominal Account to use and all transactions get magically assigned to that other account.

So how about not making that metadata internal and/or providing some interfaces to make use of it with a bit of documentation?

I think you can already do that, please search the forum for: GetAllRelations

This thread is an examples of what you might find, I've posted a code sample there that finds the count of related entities of a specific entity. http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=20597

Thank Walaa. Will have a look.