Class DbContextBase

Base class that implements the interface with the methods responsible for handling EF (Entity Framework) Core contexts.

Inheritance

System.Object

DbContextBase

Implements

IDbContext

System.IDisposable

Namespace: StoneCo.Framework.Data.SqlServer.Contexts

Assembly: cs.temp.dll.dll

Syntax

public abstract class DbContextBase : DbContext, IDbContext, IDisposable

Constructors

DbContextBase(DbContextOptions)

Default constructor that set the DbContextOptions.

warning

This instance is initialized with SetAutoDetectChanges(Boolean) equal false and SetTimeOut(Int32) equal 3 minutes.

Declaration

protected DbContextBase(DbContextOptions dbContextOptions)

Parameters

Type	Name	Description
DbContextOptions	dbContextOptions

Examples

note

This is an initial configuration that should be used as a basis for the following examples.

First let's create the model that will represent the entity within the context.

public class Foo : IAggregateRoot
{
    public int IdFoo { get; private set; }

    public string Name { get; private set; }

    public static IModelResult<Foo> New(string name)
    {
        var foo = new Foo { Name = name };

        return Validate(foo);
    }

    private static IModelResult<Foo> Validate(Foo foo)
    {
        var result = new ModelResult<Foo>();

        if (string.IsNullOrWhiteSpace(foo.Name))
            result.AddValidation("Name", "The field is required!");

        if (result.IsModelResultValid())
            result.SetModel(foo);

        return result;
    }
}

Here we have the interface for the Foo context.

public interface IDbContextFoo : IDbContext
{
}

Class that defines the Foo context by implementing the IDbContextFoo interface that was previously created.

public class DbContextFoo : DbContextBase, IDbContextFoo
{
    public DbContextFoo(DbContextOptions dbContextOptions)
        : base(dbContextOptions)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
}

For a working example, you need to create the entire dependency injection and configuration part using the Options design pattern.

note

This part of the example is not required to use the Framework.

Configuration file appsettings.json with the connection string.

{
  "ConnectionStrings": {
    "DefaultConnectionConfiguration": "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;persistsecurityinfo=True;"
  }
}

Loading the information from the configuration file above.

var configuration = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("appsettings.json", false)
    .Build();

Loading information for dependency injection.

var services = new ServiceCollection();

services
    .AddScoped<IDbContextFoo, DbContextFoo>()
    .AddDbContext<DbContextFoo>(options => 
        options.UseSqlServer(configuration.GetConnectionString("DefaultConnectionConfiguration")));

Methods

BeginTransaction()

Starts a new transaction.

Declaration

public IDbContextTransaction BeginTransaction()

Returns

Type	Description
IDbContextTransaction	A transaction against the database.

Examples

Bar class used in this example.

public class Bar
{
    private IDbContextFoo Context { get; }

    public Bar(IDbContextFoo context) => Context = context;

    public async Task SampleBeginTransaction()
    {
        using (var transaction = Context.BeginTransaction())
        {
            var result = Foo.New("Stone Co");

            if (result.IsModelResultValid())
                await Context.Foo.AddAsync(result.Model);

            await Context.SaveChangesAsync();

            transaction.Commit();
        }

    }
}

ExecuteRawSql<TValueObject>(String, IDictionary<String, Object>)

Synchronous method that execute SQL query on database and map result value to ValueObject.

Declaration

public virtual IEnumerable<TValueObject> ExecuteRawSql<TValueObject>(string sql, IDictionary<string, object> parameters = null)
    where TValueObject : ValueObject<TValueObject>

Parameters

Type	Name	Description
System.String	sql	SQL query to execute on database.
IDictionary<System.String, System.Object>	parameters	Key and value as parameter of the query.

Returns

Type	Description
IEnumerable<TValueObject>	Collection of the value object according result to query.

Type Parameters

Name	Description
TValueObject	The type of the model used in the instance of the value object.

Examples

In this example, use FooQuery class as result of the query.

public class FooQuery : ValueObject<FooQuery>
{
    public FooQuery(string name) => Name = name;

    public string Name { get; private set; }
}

Set on the DbContextFoo into method OnModelCreating(ModelBuilder modelBuilder).

modelBuilder.Query<FooQuery>();

Bar class used in this example.

public class Bar
{
    private IDbContextFoo Context { get; }

    public Bar(IDbContextFoo context) => Context = context;

    public IEnumerable<FooQuery> GetFooQuery(string name)
    {
        var parameters = new Dictionary<string, object>();

        var sql = @"SELECT name FROM Foo WHERE name like '@name'";

        parameters.Add("@name", name);

        return Context.ExecuteRawSql<FooQuery>(sql, parameters);        
    }
}

ExecuteRawSqlAsync<TValueObject>(String, IDictionary<String, Object>)

Asynchronous method that execute SQL query on database and map result value to ValueObject.

Declaration

public virtual Task<IEnumerable<TValueObject>> ExecuteRawSqlAsync<TValueObject>(string sql, IDictionary<string, object> parameters = null)
    where TValueObject : ValueObject<TValueObject>

Parameters

Type	Name	Description
System.String	sql	SQL query to execute on database.
IDictionary<System.String, System.Object>	parameters	Key and value as parameter of the query.

Returns

Type	Description
System.Threading.Tasks.Task<IEnumerable<TValueObject>>	Collection of the value object according result to query.

Type Parameters

Name	Description
TValueObject	The type of the model used in the instance of the value object.

Examples

In this example, use FooQuery class as result of the query.

public class FooQuery : ValueObject<FooQuery>
{
    public FooQuery(string name) => Name = name;

    public string Name { get; private set; }
}

Bar class used in this example.

public class Bar
{
    private IDbContextFoo Context { get; }

    public Bar(IDbContextFoo context) => Context = context;

    public async Task<IEnumerable<FooQuery>> GetFooQuery(string name)
    {
        var parameters = new Dictionary<string, object>();

        var sql = @"SELECT name FROM Foo WHERE name like '@name'";

        parameters.Add("@name", name);

        return await Context.ExecuteRawSqlAsync<FooQuery>(sql, parameters);        
    }
}

ExecuteSqlCommand(String, IDictionary<String, Object>)

Synchronous method that execute SQL command on database.

note

Use to execute store procedure or sql script to insert, update or delete.

Declaration

public virtual int ExecuteSqlCommand(string sql, IDictionary<string, object> parameters = null)

Parameters

Type	Name	Description
System.String	sql	SQL script to execute on database.
IDictionary<System.String, System.Object>	parameters	Key and value as parameter of the query.

Returns

Type	Description
System.Int32

Examples

Bar class used in this example.

public class Bar
{
    private IDbContextFoo Context { get; }

    public Bar(IDbContextFoo context) => Context = context;

    public void InsertNewFoo(string name)
    {
        var parameters = new Dictionary<string, object>();

        var sql = @"INSERT Foo (Name) VALUES (@name)";

        parameters.Add("@name", name);

        return Context.ExecuteSqlCommand(sql, parameters);        
    }
}

ExecuteSqlCommandAsync(String, IDictionary<String, Object>)

Asynchronous method that execute SQL command on database.

note

Use to execute store procedure or sql script to insert, update or delete.

Declaration

public virtual Task<int> ExecuteSqlCommandAsync(string sql, IDictionary<string, object> parameters = null)

Parameters

Type	Name	Description
System.String	sql	SQL script to execute on database.
IDictionary<System.String, System.Object>	parameters	Key and value as parameter of the query.

Returns

Type	Description
System.Threading.Tasks.Task<System.Int32>	Result of async execution.

Examples

Bar class used in this example.

public class Bar
{
    private IDbContextFoo Context { get; }

    public Bar(IDbContextFoo context) => Context = context;

    public async Task InsertNewFoo(string name)
    {
        var parameters = new Dictionary<string, object>();

        var sql = @"INSERT Foo (Name) VALUES (@name)";

        parameters.Add("@name", name);

        return await Context.ExecuteSqlCommandAsync(sql, parameters);        
    }
}

ExecuteStrategyAsync(Action)

Executes the specified asynchronous operation.

Declaration

public virtual Task ExecuteStrategyAsync(Action action)

Parameters

Type	Name	Description
System.Action	action	The strategy that will be used for the execution. A function that returns a started task.

Returns

Type	Description
System.Threading.Tasks.Task	A task that will run to completion if the original task completes successfully (either the first time or after retrying transient failures). If the task fails with a non-transient error or the retry limit is reached, the returned task will become faulted and the exception must be observed.

Examples

DbContextFoo used in this example.

public class DbContextFoo : DbContextBase, IDbContext
{
    public DbContextFoo(DbContextOptions dbContextOptions)
        : base(dbContextOptions)
    {
    }
}

Foo and Bar classes used in this example.

public class Foo
{
    public int IdFoo { get; set; }

    public string Name { get; set; }

    public IEnumerable<Bar> BarList { get; set; }
}

public class Bar
{
    public int IdBar { get; set; }

    public string Name { get; set; }

    public int IdFoo { get; set; }

    public Foo Foo { get; set; }
}

FooRepository sample.

public class FooRepository
{
    private DbContextFoo Context { get; }

    public FooRepository(DbContextFoo context) => Context = context;

    public async Task New(IEnumerable<Foo> modelList)
    {
        await Context.ExecuteStrategyAsync(async () =>
        {
            var bulkConfig = new BulkConfig { UseTempDB = true, SetOutputIdentity = true };

            var fooList = modelList.ToList();

            if (modelList.Count() > 2000)
                bulkConfig.BatchSize = modelList.Count();

            await Context.BulkInsertAsync(fooList, bulkConfig);

            bulkConfig.SetOutputIdentity = false;

            foreach (var foo in fooList)
            {
                var model = modelList.FirstOrDefault(it => it.Name == foo.Name);

                if (model == null)
                    continue;

                var barList = model.BarList.ToList();

                foreach (var bar in barList)
                    bar.IdBar = foo.IdFoo;

                await Context.BulkInsertAsync(barList, bulkConfig);
            }
        });
    }
}

GetDbSet<TEntity>()

Method that sets up the entity to be used in context.

note

Only entities that implement the IModel interface can be configured in the context.

Declaration

public virtual DbSet<TEntity> GetDbSet<TEntity>()
    where TEntity : class, IModel

Returns

Type	Description
DbSet<TEntity>	Entity used to query and save instances.

Type Parameters

Name	Description
TEntity	The type of entity being operated on by this set.

Examples

Settings on the interface IDbContextFoo.

public interface IDbContextFoo : IDbContext
{
    DbSet<Foo> Foo { get; }
}

Settings on the class DbContextFoo.

public class DbContextFoo : DbContextBase, IDbContextFoo
{
    public DbContextFoo(DbContextOptions dbContextOptions)
        : base(dbContextOptions)
    {
    }

    public DbSet<Foo> Foo => GetDbSet<Foo>();
}

SetAutoDetectChanges(Boolean)

Enable or disable the tracking from entities changes.

Declaration

public void SetAutoDetectChanges(bool enable = true)

Parameters

Type	Name	Description
System.Boolean	enable	True if enable auto detect change, otherwise, false.

Examples

Bar class used in this example.

public class Bar
{
    private IDbContextFoo Context { get; }

    public Bar(IDbContextFoo context) => Context = context;

    public void SetAutoDetectChangesFalse() => Context.SetAutoDetectChanges(false);
    //Or
    public void SetAutoDetectChangesTrue() => Context.SetAutoDetectChanges();
}

SetDeleted<TEntity>(TEntity)

Method that sets up the entity to deleted in context.

note

Only entities that implement the IModel interface can be configured in the context with with deleted status.

Declaration

public virtual void SetDeleted<TEntity>(TEntity entity)
    where TEntity : class, IModel

Parameters

Type	Name	Description
TEntity	entity	The modified entity.

Type Parameters

Name	Description
TEntity	The type of entity being operated on by this set.

Examples

Bar class used in this example.

public class Bar
{
    private IDbContextFoo Context { get; }

    public Bar(IDbContextFoo context) => Context = context;

    public void Delete(Foo foo) => Context.SetDeleted(foo);
}

SetModified<TEntity>(TEntity)

Method that sets up the entity to modified in context.

note

Only entities that implement the IModel interface can be configured in the context with with modified status.

Declaration

public virtual void SetModified<TEntity>(TEntity entity)
    where TEntity : class, IModel

Parameters

Type	Name	Description
TEntity	entity	The modified entity.

Type Parameters

Name	Description
TEntity	The type of entity being operated on by this set.

Examples

Bar class used in this example.

public class Bar
{
    private IDbContextFoo Context { get; }

    public Bar(IDbContextFoo context) => Context = context;

    public void Update(Foo foo) => Context.SetModified(foo);
}

SetTimeOut(Int32)

Set timeout to execute the commands into context instance.

Declaration

public void SetTimeOut(int timeoutInSeconds = 180)

Parameters

Type	Name	Description
System.Int32	timeoutInSeconds	Value that represent in seconds the timeout.

Examples

Bar class used in this example.

public class Bar
{
    private IDbContextFoo Context { get; }

    public Bar(IDbContextFoo context) => Context = context;

    public void SetTimeOut()
    {
        var timeoutInSeconds = 10;

        Context.SetTimeOut(timeoutInSeconds);
    }
}

UseNoLock()

Method for querying with nolock.

Declaration

public IDbContextTransaction UseNoLock()

Returns

Type	Description
IDbContextTransaction	A transaction against the database.

Examples

Bar class used in this example.

public class Bar
{
    private IDbContextFoo Context { get; }

    public Bar(IDbContextFoo context) => Context = context;

    public async Task<List<Foo>> SampleUseNoLock()
    {
        using (var transaction = Context.UseNoLock())
            return await Context.Foo.ToListAsync();
    }
}

Implements

IDbContext

System.IDisposable