zzzprojects / entityframework-plus Goto Github PK

Hi,

a simple scenario:
I have a DB (with EF5 on it) with about 300k of Products, the product data themselves is updated only once a day via Import.
I have a customer, a customer has a cart and a cart has a list of products (obviously :)) Now - maybe a bit naive - I did the following (very very simplified):
CartEntry entry = new CartEntry(); var product = db.Product.Where(p=>p.ProductId=productIdToAdd).FromCache().FirstOrDefault(); cartEntry.Product = product; db.CartEntry.Add(cartEntry); db.SaveChanges();

The "SaveChanges" however throws an error saying something like (it's in german, so a rough translation): "Can't determinate a valid order vor independent operations. The dependencies might be caused by foreign key constraints, model requirements or values generated from memory."

Any guess what I can do about this? I thought I can simply cache the product as it's basically immutable. By the way: a GREAT project!!! :)

this project is very useful!
For the custom audit entry, is there an detail example to explain how to use? I try to figure out how it works, but it does not work as expected!

public class CustomInheritAuditBlogDbContext : DbContext
{

    public DbSet<Blog> Blogs { get; set; }
    public DbSet<BlogImage> BlogImages { get; set; }

    public DbSet<CustomInheritAuditEntry> CustomAuditEntries { get; set; }
    public DbSet<AuditEntryProperty> AuditEntryProperties { get; set; }

    public CustomInheritAuditBlogDbContext()
    {           
        this.Database.EnsureDeleted();
        this.Database.EnsureCreated();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var path = @"C:\WORK\PRODUCT\data\blog.data";

        var connectionStringBuilder = new SqliteConnectionStringBuilder { DataSource = path };
        var connectionString = connectionStringBuilder.ToString();
        var connection = new SqliteConnection(connectionString);

        optionsBuilder.UseSqlite(connection);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //base.OnModelCreating(modelBuilder);
        //modelBuilder.Entity<CustomInheritAuditEntry>().HasKey(x => x.AuditEntryID);

        modelBuilder.Entity<CustomInheritAuditEntry>().HasMany(x => x.Properties)
                                  .WithOne(x => (CustomInheritAuditEntry)x.Parent);

        AuditManager.DefaultConfiguration.AutoSavePreAction = (context, audit) =>
        {
            // Use a constructor to create your own custom audit entry
            var customAuditEntries = audit.Entries.Select(x => new CustomInheritAuditEntry(x, "test"));
            (context as CustomInheritAuditBlogDbContext).CustomAuditEntries.AddRange(CustomAuditEntries);
        };


        AuditManager.DefaultConfiguration.IgnorePropertyUnchanged = false;

    }


}

 public int Update(Expression<Func<T, bool>> filterExpression, Expression<Func<T, T>> updateExpression, bool isSave)
 {
      DbSet<T> set = dbContext.Set<T>();
      set.Where(filterExpression).Update( updateExpression);
      int i= isSave ? dbContext.SaveChanges() : 0;
      return i;   
 }

I use the MySQL database update data appear the following issues

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Description] = '',

A.[Group] = 'terminal',

A.[ParameterCategory] = 'EQP1'

FR' at line 2

I have simple EF entity:
public class Client
{
public int Id { get; set; }
[StringLength(50)]
public string Name { get; set; }
[StringLength(3)]
public string CountryCode { get; set; }
}

Trying to update Name field.
ctx.Clients.Where(c => c.CountryCode == "US")
.Update(c => new Client() { Name = c.Name + " from US" },
x => { x.Executing = command => commandText = command.CommandText; });

EF throws an exception. Problem is in SQL code in row 4 - it should end with N' from US'

UPDATE A
SET A.[CountryCode] = @zzz_BatchUpdate_0,
A.[Name] =
CASE WHEN (B.[Name] IS NULL) THEN N'' ELSE B.[Name] END + N'
FROM [dbo].[Clients] AS A
INNER JOIN ( SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[CountryCode] AS [CountryCode],
[Extent1].[Timestamp] AS [Timestamp]
FROM [dbo].[Clients] AS [Extent1]
WHERE N'US' = [Extent1].[CountryCode]
) AS B ON A.[Id] = B.[Id]

Plugin is Z.EntityFramework.Plus.EF6

Hi,
If I do this:

ctx.MyEntities.FromCache(new MemoryCacheEntryOptions() { AbsoluteExpirationRelativeToNow = TimeSpan.FromSeconds(10) });

Then sleep for 2 minutes, I would expect the items to be gone from cache. But they arent:

var cache = QueryCacheManager.Cache as MemoryCache; var count = cache.Count; //1

I know the interval is set to 1 minute.
What am I doing wrong?
Thanks,

RP

1. installed ef+ by nuget in vs2015. use batchupdate
2. compiled ok, but exception was thrown with nothing message when running app
3. debug in code, and i found the source was GetInnerValues.
4. decompile Z.EntityFramework.Plus.EFCore.dll by ilspy, and get code follow

try
{
assembly = Assembly.Load(new AssemblyName("EntityFramework.MicrosoftSqlServer, Version = 7.0.0.0, Culture = neutral, PublicKeyToken = adb9793829ddae60"));
}
catch (Exception)
{
throw new Exception("");
}

and it will be more convenient if messages(not string.empty) can be shown while exception is thrown, thank you a lot

Forgive if this is an obvoius oversight,

Trying to use SQL Script (for Database First) and pulling the tables in by updating an edmx model from the database.

I am having a challenge with

AuditManager.DefaultConfiguration.AutoSavePreAction = (context, audit) =>
    (context as POCContext).AuditEntries.AddRange(audit.Entries);

Where I am getting an error

Argument 1: cannot convert from 'System.Collections.Generic.List<Z.EntityFramework.Plus.AuditEntry>' to 'System.Collections.Generic.IEnumerable<POC.Test.Data.AuditEntry>'

I appreciate the error just trying to understand the correct implementation strategy for the supplied script with e DB first apporach.

However I tried implementing the Custom solution and am hitting the SaveChangesAsync block

...
if (audit.Configuration.AutoSavePreAction != null)
        {
            audit.Configuration.AutoSavePreAction(this, audit);
            await base.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
        } 
...

howerver my save is not working.

Please advise the correct strategy for the audit entity and DB first.

Thanks

Not sure i'm completely following how this works, but if i set up my default cache policy as follows (using memory cache) in the app start-up events:

            var options = new CacheItemPolicy()
            {
                AbsoluteExpiration = DateTimeOffset.Now.AddMinutes(5)
            };

            QueryCacheManager.DefaultCacheItemPolicy = options;

EF will only cache objects for the first 5 minutes after app pool's initial start-up. After 5 minutes all objects will be inserted as expired. At least this is the behavior i'm seeing. Is there a way to configure default cache policy so that it is calculating absolute expiration from the time of cache insertion and not from the time the CacheItemPolicy instance is created.

I downloaded the latest source and when I open it up in VS 2015 Community, I get the following errors in project.json:

The dependency Microsoft.Extensions.Logging 1.0.0 in project Z.EntityFramework.Plus.EFCore does not support framework .NETFramework,Version=v4.5.1.

The dependency Microsoft.Extensions.DependencyInjection.Abstractions 1.0.0 in project Z.EntityFramework.Plus.EFCore does not support framework .NETFramework,Version=v4.5.1.

The dependency Microsoft.Extensions.DependencyInjection 1.0.0 in project Z.EntityFramework.Plus.EFCore does not support framework .NETFramework,Version=v4.5.1.

The dependency Microsoft.Extensions.Options 1.0.0 in project Z.EntityFramework.Plus.EFCore does not support framework .NETFramework,Version=v4.5.1.

The dependency Microsoft.Extensions.Caching.Abstractions 1.0.0 in project Z.EntityFramework.Plus.EFCore does not support framework .NETFramework,Version=v4.5.1.

The dependency Microsoft.NETCore.Platforms 1.0.1 in project Z.EntityFramework.Plus.EFCore does not support framework .NETFramework,Version=v4.5.1.

And am unable to build the project. Is there something I'm missing?

Hello,
I had an issue with mixing Include/FilteredInclude/OptimizedInclude + AsNoTracking + Future/FutureValue methods because all included references are null. I am not sure if this is a bug or 'by design'. Removing AsNoTracking helps to solve this and everthing seems to work fine. Also I just tried to set MergeOption to NoTracking and it didn't help. Here is example:

var usersFuture = Context.Users.Include(x => x.OrgUnit).AsNoTracking().DeferredFirst(x => x.Id == 1).FutureValue();
LazyLoading is disabled.

Could you please provide some info about this or workaround if you have some.
Thanks

Hello, I ask why (Query Cache Entity Framework) to add the data can not immediately update the cache?

I don't find a right way for execute some query in the future and in the same time cache the results. Is possible? Example:

ctx.BookingRequests.Where(x => x.Id > 1).FromCache().Future();
ctx.Bookings.Where(x => x.Name != "xxx").FromCache().Future();
ctx.Clients.Where(x => x.Phone != null).FromCache().Future();

All datetime fields generated are currently bound to the enviroment timezone. This can be a issue with a multi region application running in a server with a diferent timezone.

Consider a option to customize date and time timezones and set the UTC timezone as a default.

Both features are currently not compatible.

The QueryCache should check first if an internal method (from our library) to general query exists for the specified query instead of trying to generate a query using the GetObjectQuery method.

This internal method should work also for the standalone version.

I was playing around with the future queries support in EF+ this morning. Being that I wasn't interested in the other features of EF+ I only pulled down the NuGet package for future queries. In testing that I found that I needed the deferred queries package to support value results (such as Count()). With both packages installed I found that it wasn't possible to chain FutureValue() onto DeferredCount(). After that I uninstalled both packages and installed the full EF+ package. With that, it was possible to chain the method calls.

Being that there are pieces of Future which depend on Deferred, maybe it would make sense to have the Future package dependent on Deferred so that the full capabilities of Future can be utilized.

Hi

Have you have plane add support multiple levels for IncludeOptimized and IncludeFilter? How in standart inslude

include("Property.SubProperty")

My issue is that the I am not getting the change tracking even when I attach the object to the context. This is no way I can find to only set properties to modified and set the entire object to modified. The object is coming in from a website similar to the MVC problem. I want to only audit the properties that have changed, but all properties are set to modified. I wasn't sure if you knew of a work around. I will need to write something that can loop through the properties and check manually until EF Core has this built it. I know this is not a library specific issue, but was looking for guidance.

Sometimes a Take() clause is part of our IQueryable and if that is > BatchSize, the results of the Batch* command would be incorrect. So if a Take() is present in the query, I think the BatchSize should be ignored. Only alternative I could think of is declaring a temp table in the command text and inserting all the keys from the Take query into the temp table/table variable and then joining to that so that the initial Take() clause results remain constant throughout the query.

In any case, a possible solution of 'ignoring' BatchSize in BatchDelete.CreateCommand if there is a Take is something like the following (using string parsing, not sure if there is better way to do it via Expression Tree visiting or not).

Declare outside method:

private static Regex takeRegEx = new Regex(@"TOP \((?<top>.*?)\)", RegexOptions.Compiled );
Changes to method:

var querySelect = query.ToTraceString();
var queryParts = querySelect.Split( '\r' );
var selectClause = queryParts[ 0 ];
var batchSize = BatchSize > 0 && takeRegEx.IsMatch( selectClause )
    ? 0 // If query contains a TAKE( X ), don't want to run multiple 'batch' queries b/c subsequent queries would return new set of TAKE results
    : BatchSize;

// GET command text template
var commandTextTemplate = command.GetType().Name == "NpgsqlCommand"
    ? CommandTextPostgreSQLTemplate
    : isMySql
        ? CommandTextTemplate_MySql
        : batchSize > 0
            ? BatchDelayInterval > 0
                ? CommandTextWhileDelayTemplate
                : CommandTextWhileTemplate
            : CommandTextTemplate;

Let me know if you want me to implement this (and something similar in BatchUpdate) and issue a pull request or if you have a 'proper' way to handle this (considering different database providers and something other than string parsing).

Hi there, just discovered this library. Looks great so far!
One thing I'm not clear on is the difference between IncludeOptimized vs IncludeFilter.
Under what scenario should one be used over the other?
Are there scenarios whereby using either of these methods could result in a degraded performance over the regular include?

We are using a custom, multi-tenant implementation of EFCache (https://efcache.codeplex.com/) as our cache provider for memory and Redis. Since we added EF-Plus, and in particular IncludeOptimized, we noticed that caching no longer works.

EFCache seems to skip the TransactionInterceptor and Provider pipeline used by EFCache.

As far as I can tell, the following section in EF-Plus executes the queries:

var interceptionContext = Context.GetInterceptionContext();
using (var reader = DbInterception.Dispatch.Command.Reader(command, new DbCommandInterceptionContext(interceptionContext)))
{
     foreach (var query in Queries)
     {
           query.SetResult(reader);
           reader.NextResult();
      }
}

I'm not sure what the interception context is exactly, but is there a way that I can make EF-Plus use the EFCache transaction interceptor and cache the individual queries?

Thanks, Sebastian

Sometimes, you might have a IQueryable that has an OrderBy specified in it. As a simple example, given something like:

ctx.Jobs.Where( j => j.DateCreated > EntityFunctions.AddDays( DateTime.Today, -1 ) && j.Package != "null" )
        .OrderBy( j => j.DateCreated )
        .Delete();

When attempted to run, you get the exception:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

I've written a similar set of extensions as this library for Linq to Sql and to get around problem for me, I did some string searching/parsing. Not sure if that is best solution, but something like the following would work in the BatchDelete.CreateCommand.

var querySelect = query.ToTraceString();

var queryParts = querySelect.Split( '\r' );
var selectClause = queryParts[ 0 ];
var needsTopClause = selectClause.IndexOf( " TOP " ) < 0 && querySelect.IndexOf( " ORDER BY " ) > 0;

if ( needsTopClause )
{
    querySelect =
        string.Join(
            "\r",
            new[] { selectClause.Replace( "SELECT ", "SELECT TOP 100 PERCENT " ) }.Concat( queryParts.Skip( 1 ) )
        );
}

I'm not sure if there is a more clever way to do it via Expression tree parsing or not. Also, I'm not sure of the different variants the command text might be for different database providers. Let me know if you want me to implement ( and something similar in BatchUpdate) and issue a pull request or if you think you'll have a 'better' way of fixing.

Thanks.

Hello.

When I try to execute this code, I get null reference exception. Whats wrong?
public void RemoveRangeAsNoTracking(IEnumerable<TagTrend> trends) { var ids = trends.Select(s => s.TagTrendId).ToArray(); Context.Set<TagTrend>().Where(w => ids.Contains(w.TagTrendId)).Delete(); }
Stack trace:
в Z.EntityFramework.Plus.BatchDelete.Execute[T](IQueryable1 query)
в Z.EntityFramework.Plus.BatchDeleteExtensions.Delete[T](IQueryable1 query, Action1 batchDeleteBuilder)
в Z.EntityFramework.Plus.BatchDeleteExtensions.Delete[T](IQueryable1 query) в TankBatteryPostgreSqlEntitysLibrary.DAL.TagTrendRepository.RemoveRangeAsNoTracking(IEnumerable1 trends) в D:\ПТК РП\TankBattery\TankBatteryPostgreSqlEntitysLibrary\DAL\TagTrendRepository.cs:строка 52
в HMI.Infrastructure.DataCleaner.ClearTrends() в D:\ПТК РП\TankBattery\HMI\Infrastructure\DataCleaner.cs:строка 84`
Entity Framework Core.

Your library has been a great way for me to set-up a cache when primarily utilizing LINQ queries. The cache helps execute queries more quickly, but I would like to use it as a fallback in the case my database is down.

I would like to be able to expire my cache and recreate it, or even better, have the ability to run two cache simultaneously. This way I can constantly have a fresh cache in the case of downed database or a fallback cache that will present data regardless of it being stale. Using tags for expiration seems to also prevent any future run of that query with the same tag. Any tips or further documentation?

Thanks!

According to your documentation it is enough to just installing (nuget page) "Z.EntityFramework.Plus.QueryCache.EFCore" library. When I try to call FromCache() method I could not compile project and got warning and had to install "Z.EntityFramework.Plus.QueryDeferred.EFCore" library as well.

Is there specific situation we need to install QueryDeferred or is it a must install with Cache extensio ?

For example, in this case

dbContext.Filter<Company>(q => q.Where(c => c.Name == "abc"));

dbContext.Set<Company>().ToList();

The filter will not be applied.

There are already consumers of a context that use the generic method (UserManager for example)

In my case the fix was to override the set method and just forward the call to the property using reflection.
Basically, transformed from dbContext.Set to dbContext.Companies;

When using Include method, the cache key generation provides very bad performance.

By example, this simple LINQ make take more than 30 milliseconds to generate the key

var key = QueryCacheManager.GetCacheKey(ctx.Association_Multi_OneToMany_Lefts
    .Include(x => x.Right1s)
    .Include(x => x.Right2s)
    .Where(x => x.ID == columnId), new string[0]);

The performance hit seems to be caused by Entity Framework from the method used to retrieve the final SQL Generated and parameter.

The SQL must be generated to make the cache key unique. Using expression tree instead to construct the key may solve the issue.

Something like this:

new JobContext().Jobs.Where( j => j.DateCreated > EntityFunctions.AddDays( DateTime.Today, -1 ) && j.Package != "null" ).Update( j => new Job { DateExpires = EntityFunctions.AddDays( j.DateExpired, 10 ).Value } );

Throws error: "This function can only be invoked from LINQ to Entities.”

If I do not use the EntityFunctions.AddDays() but set to a hard coded value, it works. But how could I update a batch of dates to 'slide them all forward' (which is a common pattern for our application)?

var db= DB.Create();
long a = 8;
var val1= db.bbs_topic.Where(e => e.sectionid==a).FromCache("bbs_topic").ToList().Count; //13
var val1_old = db.bbs_topic.Where(e => e.sectionid == a).ToList().Count; //13
a = 12;
var val2 = db.bbs_topic.Where(e => e.sectionid == a).FromCache("bbs_topic").ToList().Count; //13
var val2_old = db.bbs_topic.Where(e => e.sectionid == a).ToList().Count; //5

Why ‘val2’ is equal to 13? It should be 5.

package restore failed:

Package Z.EntityFramework.Plus.EFCore 1.2.0 is not compatible with netcoreapp1.0 (.NETCoreApp,Version=v1.0). Package Z.EntityFramework.Plus.EFCore 1.2.0 supports:
- net451 (.NETFramework,Version=v4.5.1)
- netcore50 (.NETCore,Version=v5.0)
One or more packages are incompatible with .NETCoreApp,Version=v1.0.

I could not see any explanation if caching extension is thread safe.
Could you give some information if appropriate lock strategy is applied. Or do we need to take care of that part ?

And what is default cache settings if we do not specify expiration ?

Hi,

I have encountered issues with batch update and delete. It is working fine in v1.2.5, but it throw exception after v1.3.4, including 1.3.5. My project using VS2015, EF 6.1.3 and .net 4.6.1, db first. Can assist to look what is wrong, is it additional code needed after new release? Thanks.

Sample code that trigger issue:

var downloadSource = (from x in ctx.trnDownloadSources
where x.WindowsLogon.ToUpper() == clientWindowsLogon.WindowsLogonName.ToUpper()
select x);

                        if (downloadSource.Any())
                        {
                            downloadSource.Update(x => new trnDownloadSource
                            {
                                IsComplete = 3
                            });
                        }

Oops! A general error has occurred. Please report the issue including the stack trace to our support team: [email protected]

at Z.EntityFramework.Plus.Internal.Model.GetConceptualModelString(DbContext this)\r\n at Z.EntityFramework.Plus.Internal.Model.GetDatabaseFirst(DbContext context)\r\n at Z.EntityFramework.Plus.InternalExtensions.GetModel(DbContext context)\r\n at Z.EntityFramework.Plus.BatchUpdate.Execute[T](IQueryable1 query, Expression1 updateFactory)\r\n at Z.EntityFramework.Plus.BatchUpdateExtensions.Update[T](IQueryable1 query, Expression1 updateFactory, Action1 batchUpdateBuilder)\r\n at Z.EntityFramework.Plus.BatchUpdateExtensions.Update[T](IQueryable1 query, Expression`1 updateFactory)\r\n...

as the title

There is an issue on this wiki

var orders = ctx.Orders
                .Where(x => x.OrderId = myOrderID) // 1 orders, 20 columns
                .Include(x => x.Items) // 20 items, 10 columns
                .Include(x => x.DeliveredItems) // 10 items, 10 columns
                .ToList();

The where is missing an Equal (=). It should be:

var orders = ctx.Orders
                .Where(x => x.OrderId == myOrderID) // 1 orders, 20 columns
                .Include(x => x.Items) // 20 items, 10 columns
                .Include(x => x.DeliveredItems) // 10 items, 10 columns
                .ToList();

I have a problem with the audit feature.
Is it intended that the Audit-Entry doesn't have the Id of the added/modified entity? It seems quite difficult to me to get the history of an entity if the Audit-Entries don't have the Id.

Could you give me an example how I should use the DbContext to get the history of an added entity?

Currently, it's possible (Code First), to retrieve audit using Where extension methods (from EF+ library)

using (var ctx = new TestContext())
{
    ctx.AuditEntries.Where(item);
    ctx.AuditEntries.Where<Entity_Basic>(item.ID);
    ctx.AuditEntries.Where<Entity_Basic>(101);
}

However, using the same method name as LINQ add some confusion like discussed in issue #23

Another solution could be to use a method named GetAudits (or support both!)

Currently, it's possible (Code First), to retrieve audit using Where extension methods (from EF+ library)

using (var ctx = new TestContext())
{
    ctx.AuditEntries.Where(item);
    ctx.AuditEntries.Where<Entity_Basic>(item.ID);
    ctx.AuditEntries.Where<Entity_Basic>(101);
}

However, these methods don't work in Database First like discussed in issue #23 .

We must find a solution to allow Database First user to retrieve audit easily.

I'm not sure if this is by design or not. I'm using this configuration :

 audit.Configuration.Exclude(a => true);
 audit.Configuration.Include<IAuditable>();
 audit.Configuration.ExcludeProperty();

What I was trying to do was to save my audit without having anything in AuditEntryProperties beside the key properties.

What I'm getting is only data in AuditEntry and nothing in AuditEntryProperties, not even keys. Do I really have to include manually all key to get the wanted result?

Similar to the mention in #32, would it be possible to have a cache control similar to Query Filters.

• EF+ Query Cache Global (e.g. QueryCacheManager.Cache() )
• EF+ Query Cache Instance (e.g. this.Cache<Customer() )

So that caching can be transparent to the Linq/DbSet query.

Love the library BTW. Good work!

Not an issue but instead a request. I have a requirement to track ip address of the remote client in the audit. I do not see an easy way to extend the library to accomplish this and have instead had to create a quick fix from your source. I added in the Audit class and AuditEntry class.

Would you consider adding this property in future releases?

I've got a pretty funky thing going on here...

First what works -- creating new records and deleting from my dbcontext will yield correct entries into the both the AuditEntries and AuditEntryProperties tables. This is good and expected.

NOTE that the data from the class that is being modified is indeed saving all field values into the database table correctly. Again this is very good... it's just the saving of the audit data that has an issue...

What doesn't work -- When I update a recordset, only the primary key value is being saved to the AuditEntryProperties table and NO OTHER changed fields are being saved in that table. Oddly, the PK doesn't change yet it does show up as a new row in the table, but I think it should not be showing up there anyways (I have this setting: AuditManager.DefaultConfiguration.IgnorePropertyUnchanged = true). At the end of this write-up you'll see what I'm getting in the AuditEntryProperties table... you can see that a new record is added, then subsequently the same record is edited (the changed entity properties ARE NOT being written for some reason), and lastly I deleted the record and that works as well. You can tell what properties got updated from the 'oldvalue' column of the delete set of records.

Here is my setup:

I have about 50 fields in my table, but this is a jist of what it is defined as mostly...

CREATE TABLE [dbo].[ClientPricing](
[RecId] [int] IDENTITY(1,1) NOT NULL,
[CustId] nvarchar NOT NULL,
[CustomerName] nvarchar NULL,
[StandardOrderPrice] [decimal](18, 2) NULL,
[StandardLinePrice] [decimal](18, 2) NULL,
[StandardUnitPrice] [decimal](18, 2) NULL,
[MassMailOrderPrice] [decimal](18, 2) NULL,
[MassMailLinePrice] [decimal](18, 2) NULL,
[MassMailUnitPrice] [decimal](18, 2) NULL,
[BulkOrderPrice] [decimal](18, 2) NULL,
[BulkLinePrice] [decimal](18, 2) NULL,
[BulkUnitPrice] [decimal](18, 2) NULL,
[PalletPrice] [decimal](18, 2) NULL,
[CasePrice] [decimal](18, 2) NULL,
[OuterPrice] [decimal](18, 2) NULL,
[InnerPrice] [decimal](18, 2) NULL,
[EachPrice] [decimal](18, 2) NULL ......

And this is what the DBContext class looks like...

    public WebApplication4Context() : base("name=WebApplication4Context")
    {
    }

    public DbSet<ClientPricing> ClientPricing { get; set; }
    public DbSet<AuditEntry> AuditEntries { get; set; }
    public DbSet<AuditEntryProperty> AuditEntryProperties { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // See: Entity Framework Code First Automatic Migration & Existing Tables ==> https://blog.rajsoftware.com/2014/07/12/entity-framework-code-first-automatic-migration-existing-tables/
        modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

        base.OnModelCreating(modelBuilder); // DON'T REMOVE THIS!!!

        AuditManager.DefaultConfiguration.AutoSavePreAction = (context, audit) =>
        {
            // ADD "Where(x => x.AuditEntryID == 0)" to allow multiple SaveChanges with same Audit
            (context as WebApplication4Context).AuditEntries.AddRange(audit.Entries);
        };

        AuditManager.DefaultConfiguration.Exclude(x => true); // Exclude ALL
        AuditManager.DefaultConfiguration.Include<IAuditable>();
        AuditManager.DefaultConfiguration.IgnorePropertyUnchanged = true;
    }

    public override int SaveChanges()
    {
        var currentUser = System.Threading.Thread.CurrentPrincipal.Identity.Name;

        var audit = new Audit();
        audit.CreatedBy = string.IsNullOrEmpty(currentUser) ? "System" : currentUser;
        audit.PreSaveChanges(this);
        var rowsAffected = base.SaveChanges();
        audit.PostSaveChanges();

        if (audit.Configuration.AutoSavePreAction != null)
        {
            audit.Configuration.AutoSavePreAction(this, audit);
            base.SaveChanges();
        }

        return rowsAffected;
    }

    public override Task<int> SaveChangesAsync()
    {
        return SaveChangesAsync(CancellationToken.None);
    }

    public override async Task<int> SaveChangesAsync(CancellationToken cancellationToken)
    {
        var currentUser = System.Threading.Thread.CurrentPrincipal.Identity.Name;

        var audit = new Audit();

        // Access to all auditing information


        audit.CreatedBy = string.IsNullOrEmpty(currentUser) ? "System" : currentUser;
        audit.PreSaveChanges(this);

        var entries = audit.Entries;
        foreach (var entry in entries)
        {
            foreach (var property in entry.Properties)
            {
            }
        }

        var rowsAffected = await base.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
        audit.PostSaveChanges();

        if (audit.Configuration.AutoSavePreAction != null)
        {
            audit.Configuration.AutoSavePreAction(this, audit);
            await base.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
        }

        // Access to all auditing information
        entries = audit.Entries;
        foreach (var entry in entries)
        {
            foreach (var property in entry.Properties)
            {
            }
        }

        return rowsAffected;
    }
}

}

And here's the first part of my ClientPricing class... (Note that I'm inheriting from IAuditable as referenced above in the OnModelCreating method to only allow auditing on specified classes)

public partial class ClientPricing : IAuditable
{
    [Key]
    public int RecId { get; set; }
    public string CustId { get; set; }
    public string CustomerName { get; set; }
    public Nullable<decimal> StandardOrderPrice { get; set; }
    public Nullable<decimal> StandardLinePrice { get; set; }
    public Nullable<decimal> StandardUnitPrice { get; set; }
    public Nullable<decimal> MassMailOrderPrice { get; set; }
    public Nullable<decimal> MassMailLinePrice { get; set; }
    public Nullable<decimal> MassMailUnitPrice { get; set; }
    public Nullable<decimal> BulkOrderPrice { get; set; }
    public Nullable<decimal> BulkLinePrice { get; set; }
    public Nullable<decimal> BulkUnitPrice { get; set; }
    public Nullable<decimal> PalletPrice { get; set; }
    public Nullable<decimal> CasePrice { get; set; }
    public Nullable<decimal> OuterPrice { get; set; }
    public Nullable<decimal> InnerPrice { get; set; }
    public Nullable<decimal> EachPrice { get; set; }

Lastly, here's the table data...
AuditEntryID EntitySetName EntityTypeName State StateName CreatedBy CreatedDate
1 ClientPricing ClientPricing 2 EntityModified System 2016-05-25 18:11:58.163
2 ClientPricing ClientPricing 2 EntityModified System 2016-05-25 18:14:43.117
3 ClientPricing ClientPricing 0 EntityAdded System 2016-05-25 18:18:05.197
4 ClientPricing ClientPricing 2 EntityModified System 2016-05-25 18:19:59.657
5 ClientPricing ClientPricing 1 EntityDeleted System 2016-05-25 18:21:18.400

AuditEntryPropertyID AuditEntryID RelationName PropertyName OldValue NewValue
1 1 NULL RecId 3002 3002
2 2 NULL RecId 3002 3002
3 3 NULL RecId NULL 3003
4 3 NULL CustId NULL mynewcustomer
5 3 NULL CustomerName NULL blah
6 3 NULL StandardOrderPrice NULL 2
7 3 NULL StandardLinePrice NULL 3
8 3 NULL StandardUnitPrice NULL 4
9 3 NULL MassMailOrderPrice NULL
10 3 NULL MassMailLinePrice NULL
11 3 NULL MassMailUnitPrice NULL
12 3 NULL BulkOrderPrice NULL
13 3 NULL BulkLinePrice NULL
14 3 NULL BulkUnitPrice NULL
15 3 NULL PalletPrice NULL
16 3 NULL CasePrice NULL
17 3 NULL OuterPrice NULL
18 3 NULL InnerPrice NULL
19 3 NULL EachPrice NULL
20 3 NULL ExpeditedOrderFlatFee NULL
21 3 NULL ExpeditedOrderPercentFee NULL
22 3 NULL WhiteGloveFlatFee NULL
23 3 NULL WhiteGlovePercentFee NULL
24 3 NULL ShippingSerialNumberFee NULL
25 3 NULL TierCalculationFlag NULL
26 3 NULL TierCalculationPeriod NULL
27 3 NULL RoutingGuideSetup NULL
28 3 NULL RoutingGuideMaintenance NULL
29 3 NULL ThirdPartyShippingFee NULL
30 3 NULL PackagingPrice NULL
31 3 NULL CubicStoragePrice NULL
32 3 NULL StorageLocationPeriod NULL
33 3 NULL LargeLocationPrice NULL
34 3 NULL SmallLocationPrice NULL
35 3 NULL PickType1LocationPrice NULL
36 3 NULL PickType2LocationPrice NULL
37 3 NULL PickType3LocationPrice NULL
38 3 NULL PickType4LocationPrice NULL
39 3 NULL PickType5LocationPrice NULL
40 3 NULL ReturnOrderPrice NULL
41 3 NULL ReturnLinePrice NULL
42 3 NULL ReturnUnitPrice NULL
43 3 NULL RMAOrderPrice NULL
44 3 NULL RMALinePrice NULL
45 3 NULL RMAUnitPrice NULL
46 3 NULL ReturnSerialNumberFee NULL
47 3 NULL PricePerReceipt NULL
48 3 NULL PricePerUnitReceived NULL
49 3 NULL AccountManager NULL
50 3 NULL Salesman NULL
51 3 NULL PaymentTerms NULL
52 3 NULL International NULL
53 3 NULL MonthlyServicesCharge NULL
54 3 NULL MonthlyWebServiceCharge NULL
55 3 NULL Receive20ftContainerPrice NULL
56 3 NULL Receive40ftContainerPrice NULL
57 3 NULL Receive53ftContainerPrice NULL
58 4 NULL RecId 3003 3003
59 5 NULL RecId 3003 NULL
60 5 NULL CustId mynewcustomer NULL
61 5 NULL CustomerName blahblah NULL
62 5 NULL StandardOrderPrice 7.00 NULL
63 5 NULL StandardLinePrice 8.00 NULL
64 5 NULL StandardUnitPrice 9.00 NULL
65 5 NULL MassMailOrderPrice 1.00 NULL
66 5 NULL MassMailLinePrice 2.00 NULL
67 5 NULL MassMailUnitPrice 3.00 NULL
68 5 NULL BulkOrderPrice NULL
69 5 NULL BulkLinePrice NULL
70 5 NULL BulkUnitPrice NULL
71 5 NULL PalletPrice NULL
72 5 NULL CasePrice NULL
73 5 NULL OuterPrice NULL
74 5 NULL InnerPrice NULL
75 5 NULL EachPrice NULL
76 5 NULL ExpeditedOrderFlatFee NULL
77 5 NULL ExpeditedOrderPercentFee NULL
78 5 NULL WhiteGloveFlatFee NULL
79 5 NULL WhiteGlovePercentFee NULL
80 5 NULL ShippingSerialNumberFee NULL
81 5 NULL TierCalculationFlag NULL
82 5 NULL TierCalculationPeriod NULL
83 5 NULL RoutingGuideSetup NULL
84 5 NULL RoutingGuideMaintenance NULL
85 5 NULL ThirdPartyShippingFee NULL
86 5 NULL PackagingPrice NULL
87 5 NULL CubicStoragePrice NULL
88 5 NULL StorageLocationPeriod NULL
89 5 NULL LargeLocationPrice NULL
90 5 NULL SmallLocationPrice NULL
91 5 NULL PickType1LocationPrice NULL
92 5 NULL PickType2LocationPrice NULL
93 5 NULL PickType3LocationPrice NULL
94 5 NULL PickType4LocationPrice NULL
95 5 NULL PickType5LocationPrice NULL
96 5 NULL ReturnOrderPrice NULL
97 5 NULL ReturnLinePrice NULL
98 5 NULL ReturnUnitPrice NULL
99 5 NULL RMAOrderPrice NULL
100 5 NULL RMALinePrice NULL
101 5 NULL RMAUnitPrice NULL
102 5 NULL ReturnSerialNumberFee NULL
103 5 NULL PricePerReceipt NULL
104 5 NULL PricePerUnitReceived NULL
105 5 NULL AccountManager NULL
106 5 NULL Salesman NULL
107 5 NULL PaymentTerms NULL
108 5 NULL International NULL
109 5 NULL MonthlyServicesCharge NULL
110 5 NULL MonthlyWebServiceCharge NULL
111 5 NULL Receive20ftContainerPrice NULL
112 5 NULL Receive40ftContainerPrice NULL
113 5 NULL Receive53ftContainerPrice NULL

Code just like this:
EFCoreDbContext ctx = new EFCoreDbContextFactory().Create();
var query=ctx.Movies.DeferredCount();

I would like to initialize the filter in the constructor of my DbContext, but I do not see any examples of doing this. I have tried passing this to the QueryFilterManager.InitilizeGlobalFilter function, but it does not seem to do anything. Now I am using a somewhat complex scenario using an autogenerator set of pocos and an interface to create the filter, but I would think it should work. Any ideas or a simple example of how to implement in a constructor?

When I try to insert an an entity being audited, we get :
Fail: Microsoft.AspNet.Server.Kestrel[13] An unhandled exception was thrown by the application. Microsoft.Data.Entity.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Cannot insert explicit value for identity column in table 'AuditEntry' when IDENTITY_INSERT is set to OFF. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
`
We are using EF 7.0.0-rc1-final. We're just introducing Auditing to a working application. Are we missing a step? Thanks.

Hi!

When I configure EF Core to use InMemoryDatabase and use FromCache(), throws "Unable to cast object of type 'Microsoft.EntityFrameworkCore.Query.Internal.InMemoryQueryContext' to type 'Microsoft.EntityFrameworkCore.Query.RelationalQueryContext'."

In class CreateCommand, line 36: (queryContext = (RelationalQueryContext)queryContextFactory.Create();)

Use sample:

// In unit test class constructor
var serviceProvider = new ServiceCollection()
    .AddEntityFrameworkInMemoryDatabase()
    .BuildServiceProvider();

var builder = new DbContextOptionsBuilder<MyContext>();

builder
    .UseInMemoryDatabase()
    .UseInternalServiceProvider(serviceProvider);

builder
    .UseInMemoryDatabase()
    .UseInternalServiceProvider(serviceProvider);

contextInstance = new MyContext(builder.Options);

// In unit test
var list = contextInstance
    .Table
    .Where(q => q.IsActive)
    .FromCache() // <- Error happens here
    .ToList();

return list.FirstOrDefault(q => q.Id == requestedId);

I implemented soft delete for EF Core but it not works.

This is my code: https://gist.github.com/hikalkan/4b0df93c8e95b08120e8cb7cd7f7cded

It properly works for EF 6.x but not works EF Core 1.0. Do you have any idea?

Thanks.

I have a pretty simple setup here with a decent number of records (5,000-6,000) and when I use IncludeFilter with Skip and Take where I'm reaching outside of my result set, instead of returning nothing it just hangs the request.

Example:

[Order] one to many [Items]. Let's say I have 100 orders.

So if I try to take 110 orders like so, my application freezes:

db.Orders
.IncludeFilter(o => o.Items.Where(i => i.Cost >= intValue))
.OrderBy(o => o.Created)
.Skip(100)
.Take(10)
.ToList();

Yet if I just use a normal Include, like so, it works as expected (returns an empty list)

db.Orders
.Include(o => o.Items)
.OrderBy(o => o.Created)
.Skip(100)
.Take(10)
.ToList();

Currently, a cache entry can be expired used ExpireTag method.

However, there is no functionality to expire all entries.

Add ExpireAll functionality to flush all entries from the cache

QueryCacheManager.ExpireAll();

QueryFilter is not being applied to related entities. When I define a soft-delete filter, it works only when accessing the main entity set of the context. But when I access child collections of an entity, the filter is not applied.

Our Query Filter is defined like this:

ctx.Filter<HQBaseEntity>("IsDeleted", q => q.Where(x => !x.DeletedOn.HasValue));

It generally works, because the DeletedOn IS NULL is correctly applied to the WHERE clause when accessing entities like this:

var nonDeletedDocument = ctx.Documents.Where(d => d.Id == 4101);

SELECT TOP (2) 
    [Extent1].[Id] AS [Id], 
    [Extent1].[ShownDate] AS [ShownDate], 
    [Extent1].[ValidUntilDate] AS [ValidUntilDate], 
    [Extent1].[SentOn] AS [SentOn], 
....
    [Extent1].[UpdatedOn] AS [UpdatedOn], 
    [Extent1].[UpdatedBy] AS [UpdatedBy], 
    [Extent1].[DeletedOn] AS [DeletedOn], 
    [Extent1].[DeletedBy] AS [DeletedBy]
    FROM [dbo].[Document] AS [Extent1]
    WHERE ([Extent1].[DeletedOn] IS NULL) AND ([Extent1].[Id] = @p__linq__0)
-- p__linq__0: '4101' (Type = Int32, IsNullable = false)

However, EF-Plus Query Filter does not apply our Deleted filter when accessing entities like this:

var nonDeletedPositionLinks = nonDeletedDocument.DocumentPositionToDocuments;

Here is the query generated by EF-Plus Query Filter when accessing child collections. It is missing the WHERE clause for the DELETED IS NULL:

SELECT 
    [Extent1].[Id] AS [Id], 
    [Extent1].[Amount] AS [Amount], 
    [Extent1].[Order] AS [Order], 
    [Extent1].[IsCompleted] AS [IsCompleted], 
    [Extent1].[Text] AS [Text], 
....
    [Extent1].[UpdatedOn] AS [UpdatedOn], 
    [Extent1].[UpdatedBy] AS [UpdatedBy], 
    [Extent1].[DeletedOn] AS [DeletedOn], 
    [Extent1].[DeletedBy] AS [DeletedBy]
    FROM [dbo].[DocumentPositionToDocument] AS [Extent1]
    WHERE [Extent1].[DocumentId] = @EntityKeyValue1
-- EntityKeyValue1: '4101' (Type = Int32, IsNullable = false)

We were using DynamicFilters (https://github.com/jcachat/EntityFramework.DynamicFilters) before, which applies the query filter also to child collections. This filter fails, however, when used in combination with EF+.

This is how I create a DbContext for unit tests:

    protected static DbContextOptions<T> CreateNewInMemoryDbContextOptions<T>()
        where T : DbContext
    {
        var serviceProvider = new ServiceCollection()
            .AddEntityFrameworkInMemoryDatabase()
            .BuildServiceProvider();

        // Create a new options instance telling the context to use an
        // InMemory database and the new service provider.
        var builder = new DbContextOptionsBuilder<T>()
            .UseInMemoryDatabase()
            .UseInternalServiceProvider(serviceProvider);

        return builder.Options;
    }

         var newTestDbContext = new TestDbContext(CreateNewInMemoryDbContextOptions<TestDbContext>())

While executing:

newTestDbContext.Entities.Where(x => x.Id > 1).Delete();

I get Exception:

at Z.EntityFramework.Plus.InternalExtensions.GetDbContext[T](IQueryable1 source) at Z.EntityFramework.Plus.BatchDelete.Execute[T](IQueryable1 query)
.....
Result Message: Unable to cast object of type 'Microsoft.EntityFrameworkCore.Query.Internal.InMemoryQueryContextFactory' to type 'Microsoft.EntityFrameworkCore.Query.Internal.RelationalQueryContextFactory'.