neisbut / entityframework.memoryjoin Goto Github PK

Two exactly identical queries running twice are not resolving exactly the same sql because of the interlock increment sequence.

This may cause some Parameter Sniffing issues as things like sql server will cache the query plan base on the text hash so any small change would get cached differently.

cf https://dba.stackexchange.com/questions/291597/parameter-sniffing-and-multiple-plans-in-cache

This is caused by
https://github.com/neisbut/EntityFramework.MemoryJoin/blob/master/src/EntityFramework.MemoryJoin/Internal/MappingHelper.cs#L220

Trying to understand why it's here in the first place ? What is this line trying to achieve ? @neisbut

Getting exception while building the Project to upgrade dotnet 8.

The type or namespace name 'MemoryJoinerMode' could not be found (are you missing a using directive or an assembly reference?)	EntityFrameworkCore.MemoryJoin (net6.0), EntityFrameworkCore.MemoryJoin (net7.0), EntityFrameworkCore.MemoryJoin (net8.0), EntityFrameworkCore.MemoryJoin (netstandard2.1)	EntityFramework.MemoryJoin\src\EntityFrameworkCore.MemoryJoin\MemoryJoiner.cs

Feature Request – Support for Sqlite
Support Sqlite so that code that uses MemoryJoin can have Unit Tests that use Sqlite’s InMemory database rather than only having Integration Tests that use Sql Server.

Sqlite Core nuget package is here:
https://www.nuget.org/packages/Microsoft.Data.Sqlite.Core/
Source is here: https://github.com/dotnet/efcore/tree/master/src/Microsoft.Data.Sqlite.Core

I think the main problem is that Sqlite doesn't support named columns for derived tables
so
SELECT * FROM (VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) AS __gen_query_data__ (id, string1, long1, date1, string2, long2, double1, date2) WHERE 1=0

would need to become something like:

SELECT 1 as string1, 2 as long1, 3 as date1, 4 as string2, 5 as long2, 6 as double1, 7 as date2 FROM (VALUES (NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)) AS __gen_query_data__ WHERE 1=0

Additional
Support for the MS InMemory DB would be even better, but probably much harder because it doesn't support CreateCommand()
https://www.nuget.org/packages/Microsoft.EntityFrameworkCore.InMemory

Are there any plans on adding support for EF Core 5 based on .NET Standard 2.1? I would like to fork it otherwise, because it's such an essential library.

I have setup as your example but it doesn't work for me, I got the error as below. Do you have any ideas about that?

"System.InvalidOperationException: 'QueryModelClass is not found in the context. Please check configuration'"
  at EntityFramework.MemoryJoin.Internal.EFHelper.GetKeyProperty(DbContext context, Type t)
  at EntityFramework.MemoryJoin.Internal.MappingHelper.GetEntityMapping[T](DbContext context, Type queryClass, Dictionary`2 allowedPropertyMapping)
  at EntityFramework.MemoryJoin.MemoryJoiner.FromLocalList[T](DbContext context, IList`1 data, Type queryClass, ValuesInjectionMethod method)
  at EntityFramework.MemoryJoin.MemoryJoiner.FromLocalList[T](DbContext context, IList`1 data, Type queryClass)
  at EntityFramework.MemoryJoin.MemoryJoiner.FromLocalList[T](DbContext context, IList`1 data)
  at JoinMemoryListWithEF.Program.Main(String[] args) in C:\Users\Nhan.Nguyen\source\repos\JoinMemoryListWithEF\Program.cs:line 19

Below is my code:

namespace JoinMemoryListWithEF.Entity
{
    using EntityFramework.MemoryJoin;
    using System;
    using System.Data.Entity;
    using System.Data.Entity.Infrastructure;
    using System.Linq;

    public partial class ReviewManagementEntities : DbContext
    {
        public ReviewManagementEntities()
            : base("name=ReviewManagementEntities")
        {
        }
    
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }
    
        public virtual DbSet<Review> Reviews { get; set; }
        protected DbSet<QueryModelClass> QueryData { get; set; }
    }
}

class Program
    {
        static void Main(string[] args)
        {
            var coachs = GetCoachs();     //List<Coach>      

            ReviewManagementEntities context = new ReviewManagementEntities();            
            var memmoryCoach = context.FromLocalList(coachs);// I got error at here

            Console.ReadLine();
        }

        private static List<Coach> GetCoachs()
        {
            return new List<Coach>()
            {
                new Coach() { CoachID = 149, Name="ABC" },
                new Coach() { CoachID = 160, Name="OK" }
            };
        }
    }

packages.config

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="EntityFramework" version="6.2.0" targetFramework="net472" />
  <package id="EntityFrameworkCore.MemoryJoin" version="0.6.0" targetFramework="net472" />
  <package id="EntityFrameworkCore.MemoryJoin.Ef6" version="0.5.9" targetFramework="net472" />
</packages>

I've got a code first model using EntityTypeConfiguration maps that are created through reflection. I've added a DbSet to the AppContext
protected DbSet<QueryModelClass> QueryData { get; set; }

I'm getting a "Obect reference not set to an instance of an object" exception when I try to do a join.

   at EntityFramework.MemoryJoin.Internal.EFHelper.GetKeyProperty(DbContext context, Type t)
   at EntityFramework.MemoryJoin.Internal.MappingHelper.GetEntityMapping[T](DbContext context, Type queryClass, Dictionary`2 allowedPropertyMapping)
   at EntityFramework.MemoryJoin.MemoryJoiner.FromLocalList[T](DbContext context, IList`1 data, Type queryClass, ValuesInjectionMethod method)
   at EntityFramework.MemoryJoin.MemoryJoiner.FromLocalList[T](DbContext context, IList`1 data)
   at Data.AppContext.UseInMemoryList[T](List`1 inMemoryList) in H:\Development\Repos\Web\Data\AppContext.cs:line 354
   at Data.Repository`1.UseInMemoryList[TList](List`1 inMemoryList) in 

The key field property is not getting filtered out from the allowedPropertyMapping dictionary values since edmx entities do not have the KeyAttribute. Are you able to filter this out by doing something like the following in the GetEntityMapping method? I tested it out by moving the following code block just below where you get the pkColumnName and adding the filter and it worked.

var allowedProperties = new HashSet(
allowedPropertyMapping.SelectMany(x => x.Value).Where(a => !a.Name.Equals(pkColumnName, StringComparison.OrdinalIgnoreCase)).ToList());

Using EF 6.1.3

I was just doing a basic join and I got that error.
I investigated the problem and I can see that the EF generated a query which refers to the 'id' field of your fake query entity, but you didn't put that column in your table literal in the generated SQL code.

So, I changed MappingHelper so that the 'id' column is always generated in the SQL code (just with values of 1, 2, 3, 4, ...), and it works now.

Hi,

first of all: thanks for the great library!
I've got an issue with empty lists I want to join to and I suspect you already fixed it with your latest commit. However, I cannot benefit from the fix, since the nuget package is outdated. Do you have any plans to update it soon?

Many Regards,
Florian

Hello I did

var reducedData = guids.Select(x => new 
            {
              Id =  x,                
            }).ToList();

            var queryable = _dbContext.FromLocalList(guids);

I just need to pass a list of guids and returns from the table the row where the primary key is contained in the reducedData like
reducedData.contains(PK)

i get the error Exception Static field requires null instance, non-static field requires non-null instance. (Parameter 'expression')

It is when the code calls Expression.MakeMemberAccess(inParam, member); in MappingHelper.GetEntityMapping.

Can you tell me what's is wrong with my calling code? guids is a list of guids.

Thanks

This link in your readme no longer takes you to where you think it takes you:
http://tsherlock.tech/2018/03/20/joining-in-memory-list-to-entity-framework-query/

If you have an entity with a many to many relationship which is in a split table entity and you want this list included in your projection it duplicates the OrderBy.

Is there a way to extend QueryModelClass properties? For example to add and extra public string String4 { get; set; }.

Thanks in advance.

When using join with multiple local lists I get the following error:
The variable name '@__gen_q_p0' has already been declared. Variable names must be unique within a query batch or stored procedure.

I managed to track it down to the fact the MappingHelper.AppendRowsAsValues always starts numbering the parameters from 0.
I believe it should be made possible (as an overload perhaps) to pass somehow a reference index that can be used and incremented in there.
This would definitely solve the problem; what do you think?

Hi,

Do you plan to upgrade to .NET5?
In .NET CORE 3 the RawSqlString was obsolete, and in .NET5, it can not be used.

Thanks,
Gábor

We use DB2 at my place of work, I tried MemoryJoin but DB2 (or at least the version we use) uses underscores as conditional compilation directives, so the error displayed further down is thrown. Removing these from the front of the DynamicTableName string ("__gen_query_data__" becomes "gen_query_data__") seems to fix the issue! :)

My code doesn't hit param creation code using the "@__gen_q_p_" pattern, so can't tell if that would also be an issue. The QueryModelClass table and schema name also start with a double underscore, but that doesn't appear to cause a problem.

Error:
{
  "Message": "An error has occurred.",
  "ExceptionMessage": "An error occurred while executing the command definition. See the inner exception for details.",
  "ExceptionType": "System.Data.Entity.Core.EntityCommandExecutionException",

  "InnerException": {
    "Message": "An error has occurred.",
    "ExceptionMessage": "ERROR [428HV] [IBM][DB2/LINUXX8664] SQL20521N  Error occurred processing a conditional compilation directive near \"_\". Reason code=\"7\".",
    "ExceptionType": "IBM.Data.DB2.DB2Exception",
    "StackTrace": "   at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String method, DB2CursorType reqCursorType, Boolean abortOnOptValueChg, Boolean skipDeleted, Boolean isResultSet, Int32 maxRows, Boolean skipInitialValidation)\r\n   at IBM.Data.DB2.DB2Command.ExecuteReaderObject(CommandBehavior behavior, String method)\r\n   at IBM.Data.DB2.DB2Command.ExecuteReader(CommandBehavior behavior)\r\n   at IBM.Data.DB2.DB2Command.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<>c.<Reader>b__6_0(DbCommand t, DbCommandInterceptionContext`1 c)\r\n   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)\r\n   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)\r\n   at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)\r\n   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)\r\n   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)"
  }
}

Currently, when using ValuesInjectionMethod.ViaSqlQueryBody, the data types bool and Guid are not written inline, but via command parameters.

Is it possible to add support also for these 2 data types?
Thank you in advance!

If you write a query involving a "local list" and involving an initialization of that list type, there's a good chance you'll get this exception:

"The type 'XYZ' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order."

For example, there's the chance of this exception when running this query, because of the "new Key" initialization part.

context.FromLocalList(keyList)
                        .GroupJoin(
                                context.Table2,
                                k => k,
                                x => new Key { Id1 = t.Field1, Id2 = t.Field2 },
....
);

public class Key
{
  public int Id1;
  public int Id2;
}

I'm guessing that the reason for this error is that the MemoryJoin code is generating some kind of projection (like using Select), like:

.Select(new Key { ...})

Hi.
I see a commit for supporting .NET 7, but it seems not to be published to nuget packages.
The last update is Last updated 12/11/2021.

Hi,

I have an exception with .net6. The FindEntityType method is not an extension method in .net 6.
Message: 
System.MissingMethodException : Method not found: 'Microsoft.EntityFrameworkCore.Metadata.IEntityType Microsoft.EntityFrameworkCore.ModelExtensions.FindEntityType(Microsoft.EntityFrameworkCore.Metadata.IModel, System.Type)'.

Arborescence des appels de procédure: 
EFHelper.GetKeyProperty(DbContext context, Type t)
MappingHelper.GetEntityMapping[T](DbContext context, Type queryClass, Dictionary2 allowedPropertyMapping) MemoryJoiner.FromLocalList[T](DbContext context, IList1 data, Type queryClass, ValuesInjectionMethod method)
MemoryJoiner.FromLocalList[T](DbContext context, IList1 data, Type queryClass) MemoryJoiner.FromLocalList[T](DbContext context, IList1 data)

Thanks

Try it and you'll get an error from Entity Framework.

The fix should be in MappingHelper.cs >>GetEntityMapping:

Like this:

        foreach (var member in typeof(T).GetProperties().Cast<MemberInfo>().Concat(typeof(T).GetFields()))
        {
            var memberType = (member is PropertyInfo) ? ((PropertyInfo)member).PropertyType : ((FieldInfo)member).FieldType;

            var baseType = Nullable.GetUnderlyingType(memberType) ?? memberType;
            if (!allowedPropertyMapping.TryGetValue(baseType, out var allowedMappedProps))
                throw new NotSupportedException("Not supported property type");

            var mapProperty = allowedMappedProps.FirstOrDefault(x => allowedProperties.Contains(x));
            if (mapProperty == null)
                throw new NotSupportedException("Too complex object");

            Expression inExp = Expression.MakeMemberAccess(inParam, member);
            if (mapProperty.PropertyType != memberType)
                inExp = Expression.Convert(inExp, mapProperty.PropertyType);

            inMappingPairs.Add(new Tuple<MemberInfo, Expression>(mapProperty, inExp));

If you try to insert more than 2100 itemes in the FromLocalList it will error and you will need to batch and concat before the query. I tried the same using Thinktecture.EntityFrameworkCore.SqlServer BulkInsertValuesIntoTempTableAsync which worked with greater than the 2100 limit.

If you run FromLocalList, and then you run a query without that local list, you will get an ArgumentOutOfRangeException

The problem is that you don't check whether the query even has that memory list. You just assume it does.

Before you run ModifyQuery below, you should just check that the query has a memory list in it.

    private void ModifyQuery(DbCommand command, InterceptionOptions opts)
    {
        int index = command.CommandText.IndexOf(opts.QueryTableName);
        int num2 = command.CommandText.IndexOf(' ', index); // problems start here
        int num3 = command.CommandText.LastIndexOf(' ', index);

I'm sure you would do more if you had time.The 2000 parameter limit would be good to get rid of. And also, to be able to make multiple local lists and use them in any order - or not use them at all.

Would be nice if the parameters that already exists were reused.
I believe the only change required for this are to move away from IList to IList
and use this in AppendRowsAsValues

    var parameter = value?.GetType().IsValueType == true ? parameters.FirstOrDefault(x => x.Value == value) : null;
                        var paramName = parameter?.ParameterName ?? $"{paramPattern}{i}";

                        if (parameter == null)
                        {
                            var param = command.CreateParameter();
                            param.ParameterName = paramName;
                            param.Value = value;
                            parameters.Add(param);
                            i++;
                        }

                        sb.Append(paramName);

Currently, the .FromLocalList methods only accept an IList<T>.

Since this package is converting an in-memory collection, I see no reason why an IEnumerable<T> or something slightly lower down cannot be used instead.

IEnumerable would be ideal, but something like ICollection would also work.

In your generated code - the VALUES part - when it involves numbers (ints or longs), the type of the column is inferred to be an int (unless any of the numbers are bigger than 10^31 I guess).

This is not good when the value should actually be a long (and not an int), because EF can throw this exception:

"The specified cast from a materialized 'System.Int32' type to the 'System.Int64' type is not valid."

Because EF is expecting certain result columns to be bigints, but instead it gets int columns back from the database.

So, you might want to write literal longs as "cast(12345 as bigint)" instead of just "12345".
(This fixed the problem for me.)

With Core 3.0 it is possible to make use of EF6.3. Unfortunately, the EF6 version of MemoryJoin is not compatible yet. Do you have any plans to change that?

I'm experiencing an issue similar to this issue whereby passing a seemingly simple object into FromLocalList results in an error, "Too complex object."

My project is configured for .NET 4.7.2 using EF 6:

  <package id="EntityFramework" version="6.4.4" targetFramework="net472" />
  <package id="EntityFrameworkCore.MemoryJoin" version="0.7.5" targetFramework="net472" />

Any idea what makes this array (which is not so far from the example) too complex for the method to handle? Is .NET 4.7.2 not supported?

    var queryData = new[] {
        new { 
            ProductType = "Hardwood", 
            Manufacturer = "Mohawk Industries", 
            ProductLineName = "Santa Barbara", 
            Price = "10.01",
            NormalizedUOM = "SQ FT"
        },
        new {
            ProductType = "Carpet",
            Manufacturer = "Mohawk Industries",
            ProductLineName = "Celestial Beauty",
            Price = "9.01",
            NormalizedUOM = "SQ FT"
        }
    };

    var items = Context.FromLocalList(queryData);

I have also tried the following but the error returns, "Please include at least one property with Long type."

    var queryData = new ImportModel2[] {
        new ImportModel2 { 
            ProductType = "Hardwood", 
            Manufacturer = "Mohawk Industries", 
            ProductLineName = "Santa Barbara", 
            Price = "10.01",
            NormalizedUOM = "SQ FT"
        },
        new ImportModel2 {
            ProductType = "Carpet",
            Manufacturer = "Mohawk Industries",
            ProductLineName = "Celestial Beauty",
            Price = "9.01",
            NormalizedUOM = "SQ FT"
        }
    };

    var items = Context.FromLocalList(queryData, typeof(ImportModel2));

The class definition of ImportModel2 is as follows:

    public class ImportModel2
    {
        public string ProductType { get; set; }
        public string Manufacturer { get; set; }
        public string ProductLineName { get; set; }
        public string StyleNumber { get; set; }
        public string Width { get; set; }
        public string UOM { get; set; }
        public string Price { get; set; }
        public string NormalizedUOM { get; set; }
    }

I have a simple query which joins on an in-memory list.

`var taxTableIds = taxTables.Select(a => new { TaxTableId = a.Id }).ToList();
var ttQueryable = _dbContext.FromLocalList(taxTableIds);

            var taxBrackets = await (from tb in Retrieve<Data.Models.Tax.TaxBrackets>()
                                     join tt in ttQueryable on tb.TaxTableId equals tt.TaxTableId
                                     select tb)
                                     .ToListAsync();

            var taxBracketsPublish = taxBrackets.Select(a => new TaxBracketsPublish
            {
                PublishId = publishLog.Id,
                StoreNumber = storeHelper.StoreNumber,
                SatelliteNumber = storeHelper.SatelliteNumber,
                Id = a.Id,
                TableId = a.TaxTableId,
                LeftValue = a.LeftValue,
                RightValue = (decimal)a.RightValue,
                TaxValue = a.TaxValue,
                StartRepeat = a.StartRepeat,
                RestaurantId = storeHelper.RestaurantId
            }).ToList();

            return taxBracketsPublish;`

However, I'm getting an "Incorrect syntax near 'LIMIT'.", which is strange, because I don't believe LIMIT syntax is even supported in T-SQL.

The query produced the LINQ does indeed have LIMIT syntax:
SELECT [tb].[Id], [tb].[LastModifiedByUserId], [tb].[LastModifiedDT], [tb].[LeftValue], [tb].[RightValue], [tb].[StartRepeat], [tb].[TaxTableId], [tb].[TaxValue], [tb].[TransactionId], [t].[TaxTableId] FROM [Tax].[TaxBrackets] AS [tb] INNER JOIN ( SELECT CAST([x].[long1] AS int) AS [TaxTableId] FROM ( SELECT * FROM (VALUES (NULL, NULL)) AS __gen_query_data__ (id, long1) LIMIT 0 ) AS [x] ) AS [t] ON [tb].[TaxTableId] = [t].[TaxTableId]

I suspect that the issue here (as opposed to in other instances where this same construction appears to work fine) is that the taxTables array has 0 entries.

Is there any way to work around this?