Subsequent count operations throws multipart identifier ... could not be bound error

[ALMMa]

Affected version: 1.7.0 (this works with 1.6.3).

I'm able to demonstrate the issue, though I still don't fully get what's happening and why alias generation/mapping is not working anymore.

Consider the code (sligthly simplified):

public IEnumerable<MyType> PageItems(int page, int recordsPerPage, string filter, out int totalFilteredRecords)
{
   var db = GetOpenConnection();

   var filterPredicate = Predicates.Field<MyType>(_x => _x.SomeProperty, Operator.Eq, 'N');
   if (!string.IsNullOrWhiteSpace(filter))
   {
      filterPredicate = Predicates.Group(
         GroupOperator.And,
         filterPredicate,
         Predicates.Group(
            GroupOperator.Or,
            Predicates.Field<MyType>(_x => _x.Name, Operator.Like, filter),
            Predicates.Field<MyType>(_x => _x.Description, Operator.Like, filter)
         )
      );
   }

   var sortPredicate = Predicates.Sort<MyType>(_x => _x.Id);

   totalFilteredRecords = db.Count<MyType>(filterPredicate);
   var pagedRecords = db.GetPage<MyType>(filterPredicate, new[] { sortPredicate }, page, recordsPerPage).ToArray();

   return pagedRecords;
}

This code is inside a scoped repository class and the response is used on the controller response to build the UI (Razor) including a paging mechanism. It's entirely on AspNet MVC Core (5.0) and nothing should be stateful here.

When I run this code for the first time I get a count query which is similar to:

SELECT COUNT(*) AS [Total] FROM [MyTable]
 WHERE ((([MyTable].[Something] = @SOMETHING_0) AND ([MyTable].[SomethingElse] IN (/* ... */))) AND ([MyTable].[OtherThing] IS NOT NULL) AND ([MyTable].[OtherThing] <> @OTHERTHING_9))

However, on the second time we go to the same screen or if we refresh the page there, the cound SQL generated now looks like this:

SELECT COUNT(*) AS [Total] FROM [MyTable]
 WHERE ((([y_1].[Something] = @SOMETHING_0) AND ([y_1].[SomethingElse] IN (/* ... */))) AND ([y_1].[OtherThing] IS NOT NULL) AND ([y_1].[OtherThing] <> @OTHERTHING_9))

As you can see, now all properties are prefixed with the automatic table alias (y_1) but not the table. This causes the notorious multipart identifier... error.

Somehow I managed to fix this by forcing a cleanup on the table mapping right before we start to build the count command here, as follows:

classMap.GetType().GetProperty("SimpleAlias").SetValue(classMap, null, null);

This seems to work at least on my case, but I can't guarantee it won't have side effects with others, mainly for more complex cases. Can you please advise on what I should look into, to better troubleshoot this?

[valfrid-ly]

Sorry for the delay. Could you provide more details, like which is the database so I could try to replicate?

[ALMMa]

Sorry for the delay.

Here's some tech details:

• AspNet on .NET 5.0
• SQL Server 2019 installed on Windows Server 2019 (virtualized). All patches applied on both Windows and SQL Server.
• Dapper 2.0.123
• DapperExtensions 1.7.0

I have a page upon which I need to display a small subset of data, as well as a paging mechanism.
To be able to perform that, I first check all filters and everything, apply the count, then retrieve the paged data. And manually a ViewModel object is composed in order to have these filled for the UI.

I just moved between countries so it took a while to come back. I'm already pushing apps from 5.0 to 6.0, will re-validate and if the issue persists, will send you detailed instructions on how to reproduce. If possible, I'll see if I can build a sample app that demonstrates this.

[thefat32]

I can confirm this line is breaking subsequent Count operations. ClassMapper SimpleAlias is modified using Reflection. After this, every count on that table that doesn't have a Select to the same table before it, will break. If there is a Select to the same table before the Count it will work because table alias will be set to the same value as ClassMapper.SimpleAlias (this made the bug difficult to track because it may get you to think it happens randomly).

It does not make sense to me that the SQL Generator needs to modify the ClassMapper. But I can't fully understand the usefulness of the mentioned line.