Execute Raw Sql Queries

The DotNetToolkit.Repository supports the ability to execute raw SQL queries.

Execute Sql Query

// Initialize the repo
var options = new RepositoryOptionsBuilder()
    .UseEntityFramework<TestAppDbContext>()
    .Options;

var repo = new Repository<Customer>(options);

// The ExecuteSqlQuery is able to take in a function parameter for mapping a data reader into the new entity object
var entitiesInDb = repo.ExecuteSqlQuery(@"
    SELECT
        [C].[Id] AS [Id],
        [C].[Name] AS [Name]
    FROM [dbo].[Customers] AS [C]",
    reader => new Customer {
        Id = reader.GetInt32(0),
        Name = reader.GetString(1)
    }
);

Execute Sql Command

// Initialize the repo
var options = new RepositoryOptionsBuilder()
    .UseEntityFramework<TestAppDbContext>()
    .Options;

var repo = new Repository<Customer>(options);

// The ExecuteSqlCommand is able to return the number of rows that were updated by the executed sql query
repo.ExecuteSqlCommand(@"
    DELETE FROM [dbo].[Customers] AS [C]
    WHERE [C].[Id] = 1"
);

// We can even call a stored procedure!
repo.ExecuteSqlCommand(@"dbo.DeleteCustomerById(1)");