EF Core

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)

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

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)

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.

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)

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 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() };

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