EF Core

Instruct EF Core to create a migration named InitialCreate:

Add-Migration InitialCreate

EF Core will create a directory called Migrations in your project, and generate some files. It's a good idea to inspect what exactly EF Core generated - and possibly amend it - but we'll skip over that for now.

Create a database and create your schema from the migration. This can be done via the following:

Update-Database

That's all there is to it - your application is ready to run on your new database, and you didn't need to write a single line of SQL. Note that this way of applying migrations is ideal for local development, but is less suitable for production environments - see the Applying Migrations page for more info.

You can list all existing migrations as follows:

Get-Migration

You can remove the latest migration with the following command:

Remove-Migration

Update-Database -Migration:"NAME" 

The DbSet.FromSqlRaw method (DbSet.FromSql prior to Entity Framework Core 3.0) enables you to pass in a SQL command to be executed against the database to return instances of the type represented by the DbSet:

public class Book
{
    public int BookId { get; set; }
    public string Title { get; set; }
    public Author Author { get; set; }
    public int AuthorId{ get; set; }
    public string Isbn { get; set; }
}

...

public class SampleContext : DbContext
{
    public DbSet<Book> Books { get; set; }
}
...

using (var context = new SampleContext())
{
    var books = context.Books.FromSqlRaw("SELECT BookId, Title, AuthorId, Isbn FROM Books").ToList();
}

The DbSet must be included in the model (i.e. it can not be configured as Ignored). All columns in the target table that map to properties on the entity must be included in the SQL statement. The column names must match those that the properties are mapped to. Property names are not taken into account when the results are hydrated into instances of the entity.

If any columns are missing, or are returned with names not mapped to properties, an InvalidOperationException will be raised with the message:

'The required column '[name of first missing column]' was not present in the results of a 'FromSqlRaw' operation.'

Consider a Student entity:

public class Student

{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public int Standard { get; set; }
    public string Address { get; set; }
}

To get details of all students that are in Standard 10 you can execute an SQL query using FromSqlRaw() method like this:

var context = new SchoolContext();
var students = context.Student.FromSqlRaw("Select * from Student where Standard = 10").ToList();

Here your raw query – Select * from Student where Standard = 10 will be executed on the database and will give a list of all students that are in ‘standard 10’.

The following code shows how to execute Parameterized Query with FromSqlRaw method. It will give all the students that have a name as Tony.

var context = new SchoolContext();
string name = "Tony";
var students1 = context.Student.FromSqlRaw($"Select * from Student where Name = '{name}'").ToList();

Using LINQ Operators with “FromSqlRaw” method

You can also use LINQ Operators after the result from FromSqlRaw() method.

The below code contains the .OrderBy() LINQ Operator that gives the result in ascending order of Student’s name.

var context = new SchoolContext();
var students = context.Student.FromSqlRaw("Select * from Student").OrderBy(x => x.Name).ToList();

Including related data

The Include method can be used to include related data.

var stuTeacher = context.Student
                        .FromSqlRaw($"SELECT * FROM Student")
                        .Include(b => b.Teacher)
                        .ToList();

EF Core Migrations is a feature that helps manage database schema changes. It allows developers to easily create, update, and rollback database migrations using a code-first approach, ensuring that your database schema stays in sync with your application models.

In EF Core you can select specific columns from a database table using the Select() method with an anonymous type. This allows you to specify only the necessary columns, improving performance by reducing unnecessary data retrieval. Here's a sample code snippet:

var query = dbContext.TableName
                .Where(condition)
                .Select(x => new 
                {
                    x.ColumnName1,
                    x.ColumnName2,
                    // Add more columns as needed
                })
                .ToList();

This technique is beneficial for optimizing data retrieval in Entity Framework queries. For more details, refer to the following websites.

• How to Select Specific Columns in an Entity Framework Query - Brent Ozar Unlimited®
• Select specific properties from include ones in entity framework core - Stack Overflow
• c# - Select only specific fields with Linq (EF core) - Stack Overflow

Updating data using EF Core is straightforward. Retrieve the entity, modify its properties, and call `SaveChanges()`. EF Core automatically generates the appropriate SQL statements to update the corresponding record in the database, making data updates a breeze.

To only update one field, we can simply change the update method to the following:

Person person = new Person {Id=4, Lastname="Miller"};
dbContext.Attach(person);
dbContext.Entry(person).Property(p => p.Lastname).IsModified = true;
dbContext.SaveChanges();

The above function first constructs the object with the specified Id and updated Lastname, and then appends the object; it then explicitly marks the Lastname property as modified.  

The generated UPDATE statement now looks like this

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (8ms) [Parameters=[@p1='?' (DbType = Int32), @p0='?' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      UPDATE `Persons` SET `Lastname` = @p0
      WHERE `Id` = @p1;
      SELECT ROW_COUNT();

As shown in the EFCore log, only the Lastname field is updated.  

This approach can slightly improve performance because the SQL statement is smaller and the query execution on the database server can be faster.

See also the discussion at Stack Overflow How to update not every fields of an object using Entity Framework and EntityState.Modified

Entity Framework Core 5 is the first EF version to support filtering in Include.

Supported operations are Where, OrderBy(Descending)/ThenBy(Descending), Skip, Take

Some usage example

context.Customers
    .Include(c => c.Orders.Where(o => o.Name != "Foo")).ThenInclude(o => o.OrderDetails)
    .Include(c => c.Orders).ThenInclude(o => o.Customer)

Only one filter is allowed per navigation, so for cases where the same navigation needs to be included multiple times (e.g. multiple ThenInclude on the same navigation) apply the filter only once, or apply exactly the same filter for that navigation.

To do a case-sensitive search in EF Core you can use an explicit collation in a query like

var customers = context.Customers
.Where(c => EF.Functions.Collate(c.Name, "SQL_Latin1_General_CP1_CS_AS") == "John")
.ToList();

Note: EF Core does not support operators on in-memory collections other than simple Contains with primitive values.

References

• Collations and case sensitivity - EF Core | Microsoft Learn
• Collation Definitions | Microsoft Learn
• SQL Server collations | Microsoft Learn

Resources

• c# - Entity Framework core - Contains is case sensitive or case insensitive? - Stack Overflow
• c# - EF Core query using String.Contains - Stack Overflow

Entity Framework Core (EF Core) does wrap the SaveChanges method in a transaction by default. When you call SaveChanges to persist changes to the database, EF Core ensures that all the changes are committed as a single transaction. This means that if any part of the operation fails (e.g., due to a validation error or a database constraint violation), none of the changes will be applied to the database.

Here's how it works:

1. You make changes to your entity objects within a DbContext.
2. When you call SaveChanges, EF Core starts a database transaction.
3. EF Core applies all the changes to the database within this transaction.
4. If all changes are successfully applied, the transaction is committed, making the changes permanent.
5. If any part of the operation fails (e.g., an exception is thrown), the transaction is rolled back, and no changes are applied to the database.

This behavior ensures that your data remains in a consistent state, and either all changes are applied or none are. If you need more control over transactions, such as specifying isolation levels or manually managing transactions, EF Core provides options for doing so. You can use methods like BeginTransaction, Commit, and Rollback on the DbContext's Database property to work with transactions explicitly.

In this example, you can see how you can manually manage a transaction around your database operations, providing more fine-grained control when needed. However, for most scenarios, the default behavior of wrapping SaveChanges in a transaction is sufficient.

using (var dbContext = new YourDbContext())
{
    using (var transaction = dbContext.Database.BeginTransaction())
    {
        try
        {
            // Perform your database operations here

            dbContext.SaveChanges();

            // If everything is successful, commit the transaction
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // Handle exceptions and optionally roll back the transaction
            transaction.Rollback();
        }
    }
}

In this example, you can see how you can manually manage a transaction around your database operations, providing more fine-grained control when needed. However, for most scenarios, the default behavior of wrapping SaveChanges in a transaction is sufficient.

Make EF Core update only the updated properties by turning the disconnected scenario to connected.

public void SaveBook(Book book)
{
    // Here, 'book' is the book with the changed Title.
    using(var context = new TestContext())
    {
        var dbBook = context.Books.Find(book.ID);

        // Copy book's property values to dbBook.
        context.Entry(dbBook).CurrentValues.SetValues(book);

        context.SaveChanges();
    }
}

There may be good reasons to prefer the latter method above the former.

Ordering in LINQ allows you to sort data based on specific criteria. The OrderBy method is used to sort elements in ascending order, and ThenBy is used for secondary sorting.

// Example usage in LINQ query
var orderedList = myList.OrderBy(x => x.Col1).ThenBy(x => x.Col2).ToList();

This code snippet OrderBy(x => x.Col1).ThenBy(x => x.Col2) sorts a collection first by Col1 and then by Col2.

It's important to note that this is a LINQ feature and not exclusive to Entity Framework.

For more information, refer to discussions on Stack Overflow:

• c# - "Order by Col1, Col2" using entity framework - Stack Overflow
• Multiple "order by" in LINQ - Stack Overflow
• Enumerable.OrderBy Method (System.Linq) | Microsoft Learn
• Enumerable.ThenBy Method (System.Linq) | Microsoft Learn

To set a default schema name for your database context in Entity Framework using C#, follow these steps:

1. Open your DbContext class.
2. Override the OnModelCreating method.
3. Use modelBuilder.HasDefaultSchema("YourSchemaName") to configure the default schema.

Configure the default schema in OnModelCreating method

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    // Configure default schema
    modelBuilder.HasDefaultSchema("Sales");
}

This ensures that tables created by Entity Framework will use the specified schema (Sales in this case).

For further reference, you can check out the discussion on c# - Setting schema name for DbContext - Stack Overflow

To create a Custom Migration in EF Core, follow these steps.

Create an empty migration using the command

dotnet ef migrations add "migration name"

Inside the Up(MigrationBuilder migrationBuilder) method in the generated migration file, add your custom SQL statements using migrationBuilder.Sql.

public partial class CustomMigration : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("CREATE TABLE CustomTable (Id int, Name varchar(50));");
        migrationBuilder.Sql("INSERT INTO CustomTable (Id, Name) VALUES (1, 'Example');");
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("DROP TABLE CustomTable;");
    }
}

For more detailed information, refer to Custom Migrations Operations - EF Core | Microsoft Learn

The provided code snippet demonstrates a LINQ query to group by multiple columns while also finding the maximum item ID within each group. Here's a breakdown of the code:

var result = from o in context.Orders
             group o by new { o.CustomerId, o.OrderType } into g
             select new
             {
                 CustomerId = g.Key.CustomerId,
                 OrderType = g.Key.OrderType,
                 MaxItemId = g.Max(x => x.ItemId)
             };

In this code:

• The group by new { o.CustomerId, o.OrderType } syntax allows grouping by a combination of CustomerId and OrderType using an anonymous type
• The into g clause signifies that the grouped data will be accessible through the identifier g
• The g.Key property allows access to the grouped key values, such as CustomerId and OrderType.
• select new { ... } creates a new anonymous object for each group containing CustomerId, OrderType, and the maximum ItemId
• The Max() method is used to find the maximum ItemId within each group (g.Max(x => x.ItemId))

This code efficiently retrieves the maximum ItemId for each unique combination of CustomerId and OrderType in the orders collection.

To inspect the SQL generated by your LINQ query, you can convert the LINQ query to a SQL string using the ToQueryString() method.

// Example LINQ query
var query = from o in context.Orders;

// Convert LINQ query to SQL string
string sqlQuery = query.ToQueryString();
Console.WriteLine(sqlQuery);

This code snippet demonstrates how to generate and inspect the SQL query string using LINQ in C# useful for debugging and optimization purposes.

• EF Core 5.0: Using ToQueryString() method to translate LINQ query to SQL (gunnarpeipman.com)
• Introducing EF Core 5 Features: Using ToQueryString to get generated SQL | 1unicorn2 (oneunicorn.com)

In EF Core, joining a subquery with two columns (INNER JOIN table) can be achieved using LINQ syntax. Below is an example of how to do this:

var query = from user in context.Users
            join post in context.Posts
            on new { UserId = user.Id, IsPublished = true }
            equals new { post.UserId, IsPublished = true }
            select new
            {
                user.Username,
                post.Title
            };

In this example

• The LINQ query joins the Users and Posts tables on two columns (UserId and a condition IsPublished) using the equals keyword.
• In a LINQ join operation, you need to match corresponding columns or properties from the joined tables/entities. In the case of UserId = user.Id, you are matching the UserId column from the Post entity with the Id column from the User entity.

Additional reading at EF Core Join Query - TekTutorialsHub

To join a subquery (INNER JOIN table) in EF Core, you can use the Join method along with LINQ expressions. Below is a example code snippet:

var query = from order in context.Orders
            join orderItem in context.OrderItems
            on order.OrderId equals orderItem.OrderId
            where order.CustomerName == "John Doe"
            select new
            {
                order.OrderId,
                order.CustomerName,
                orderItem.ProductName,
                orderItem.Price
            };

The expression on order.OrderId equals orderItem.OrderId is used to specify the join condition between two tables/entities (Orders and OrderItems  based on their related columns OrderId.

Additional Reading at EF Core Inner Join (csharptutorial.net)

To group in EF Core using LINQ query syntax, use the group by clause followed by the grouping key and aggregate functions as needed. For example, to group a collection of items by a property called "Category" and count the items in each group:

var groupedData = from item in dbContext.Items
                  group item by item.Category into grouped
                  select new { Category = grouped.Key, Count = grouped.Count() };

This code sample show how to insert an entity Element without updating related entities.

_dbContext.Entry<Element>(element).State = EntityState.Added;

// Set the state of the Category navigation property of 'element' to Unchanged
_dbContext.Entry<Category>(element.Category).State = EntityState.Unchanged;

// Detach all roles associated with the 'element' from the DbContext
element.Roles.ToList().ForEach(r => _dbContext.Entry<Role>(r).State = EntityState.Detached);

// Mark the Roles collection of 'element' as not modified to prevent updating roles
_dbContext.Entry<Element>(element).Collection(r => r.Roles).IsModified = false;

await _dbContext.SaveChangesAsync();

• The element's associated Category object to Unchanged in the _dbContext. This indicates that the Category object is not modified and should not be updated in the database.
• For each Role, it sets the state to Detached. Detaching an entity means it is no longer tracked by the EF Core context for changes.
• The IsModified property of the Roles collection in the element is explicitly set to false. This indicates to EF Core that the Roles collection has not been modified and should not be updated in the database.

Entity Framework Core 9.0, geplant für November 2024, bringt zahlreiche Neuerungen mit sich. Die Preview-Versionen 1 bis 6 haben bereits verschiedene Verbesserungen eingeführt. Ein zentrales Anliegen ist die Kompatibilität mit dem Native-AOT-Compiler, die sich jedoch noch in der Entwicklung befindet. EF Core 9.0 wird sowohl auf .NET 9.0 als auch auf .NET 8.0 laufen. Im Gegensatz zu EF Core 8.0 erhält die Version 9.0 lediglich einen Standard-Term-Support von 18 Monaten anstelle des Long-Term-Supports von 36 Monaten.

Quelle: AOT noch in Arbeit (dotnetpro.de)

Entity Framework Core provides mechanisms for executing raw SQL queries directly against the database in circumstances where you cannot use LINQ to represent the query (e.g. a Full-Text Search), if the generated SQL is not efficient enough, if you want to make use of existing stored procedures, or if you just prefer to write your own queries in SQL.