Category: Entity Framework

Get Records in one table with a Foreign Key to a related one to many table

There are times when you need to get records in one table with a foreign key to a related one to many table. This is a difficult need to describe, so I’ll give you the exact business scenario.

I have designed and used a Process Tracking system for many years. It currently has two basic components in the database:

  1. A FileProcess table that tracks a file (name, date, paths, app that processed it, etc.)
  2. A StatusLog table that I punch in records as this file goes through the process of being imported, validated, etc.

Often, I have multiple applications that process a batch of records from a file. I designed a stored procedure that would allow me to check for any file, by a particular application, that was in a particular status, but not past that status.

So here’s the scenario, we have a process that I have assigned the following status log values:

10 – File Parsed
20 – File Imported
30 – Data Validated
40 – Data Archived

Ok, so one application parses the file and imports it, let’s say it’s an SQL SSIS package just for fun. So it punches two status records in while it’s working, a 10 and a 20.

So now I have another validation application that checks every few minutes for something to do. I want it to be able to find any file that is in a status of 20, but NOT higher than that. So then I know it’s ready to be validated.

In order to do this, I have the following LINQ to SQL query that seems to do the job for me. I hope looking at this code will help you with whatever similar type of issue you’re trying to solve:

public async Task<List<FileProcess>> GetFileProcessesForAStatusByAppIdAsync(int AppId, int StatusId)
        {
            try
            {
                var _entityrows = (from st in _appLogContext.StatusLogs
                                   join fp in _appLogContext.FileProcess.Include(a => a.App) on st.FileProcessId equals fp.Id
                                   where st.AppId == AppId
                                    && st.StatusId == StatusId
                                    && st.StatusId == (_appLogContext.StatusLogs.Where(f => f.FileProcessId == fp.Id).OrderByDescending(p => p.StatusId).FirstOrDefault().StatusId)
                                   select fp).AsNoTracking();


                return await _entityrows.ToListAsync();

            }
            catch (Exception)
            {
                throw;
            }
        }

For those of you that are database jockeys, here’s the SQL code that this replaces:

     @AppId AS INT = NULL,
     @StatusId AS INT = NULL

    SELECT 
        [Id],
        [AppId],
        [FileName],
        [DateProcessed],
        [Inbound]
    FROM
        [FileProcess]
    WHERE
        Id IN (
    SELECT
        s.FileProcessId
    FROM
        (SELECT DISTINCT MAX(StatusId) 
            OVER(PARTITION BY FileProcessId) 
            AS ProperRow, FileProcessId, AppId
            FROM StatusLogs) AS s
    WHERE 
        s.ProperRow = @StatusId 
        AND AppId = @AppId
        )

The instance of entity type cannot be tracked because another instance with the same key value for {‘Id’} is already being tracked

When using Entity Framework (EF) Core, by default, EF Core will track any records that it pulls from the database so that it can tell if it has changes when you go to save it again. If you attempt to add the same record again etc, it will complain with a “The instance of entity type cannot be tracked because another instance with the same key value for {‘Id’} is already being tracked” error.

If you do N-Tier development, then having EF track your objects in the Repository or DataLayer of your API is of no use. It will start to cause problems when you go to save the object through a different endpoint that has created a copy of the repository model and a SaveChanges() is attempted.

In order to work around this, you can declare the Dependency Injected (DI) instance of your DB context to not use Query Tracking by using this type of code in your Startup.cs.

services.AddDbContext<AppLogContext>(o => 
o.UseSqlServer(_AppLoggingConnString)
.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking));

You can also accomplish this on each and every query, especially if your not using .NET Core and/or Dependency Injection as:

var _entityrows = (from al in _ale.AppLogs
                   select al).AsNoTracking();

You also have the option to set this behavior on the context at some other point in your code:

 _ale.AppLogs.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;

Thanks for Reading!

Paging with Entity Framework (Advanced)

This example is only called “Advanced” because it addresses multiple tables for paging. This will often be the case, but you can view a simple example that addresses one table here: Paging with Entity Framework (Simple)

The example below accepts all the common parameters and handles assembling a Linq query. It DOES NOT execute the query until the entire statement is built. There is a necessary evil call to get the count, once the WHERE clause criteria is completed. You’ll notice that this call is done before adding the OrderBy so as not to burden the Count query with sorting.

The OrderBy statement is not standard in Linq. It uses an extension method that you can take a look at here: OrderBy Extension for Linq Queries

You’ll also notice that this method returns a PagedResult object. This is just a custom DTO that brings back a strongly typed list and a total count. You can see the code for this object here. PagedResult for Paging

        /// <summary>
        /// Complicated Paged results from multiple tables in EF Query
        /// Yasgar Technology Group, Inc. - www.ytgi.com
        /// </summary>
        /// <param name="PageNumber"></param>
        /// <param name="PageSize"></param>
        /// <param name="SearchTerm"></param>
        /// <param name="SearchFilter"></param>
        /// <param name="SortExpression"></param>
        /// <param name="SortOrder"></param>
        /// <param name="ActiveOnly"></param>
        /// <returns></returns>
        public PagedResult<Models.SalesProduct> GetSalesDetailsByEF(int PageNumber,
                                                                    int PageSize,
                                                                    string SearchTerm,
                                                                    string SearchFilter,
                                                                    string SortExpression,
                                                                    string SortOrder,
                                                                    bool ActiveOnly)
        {
            try
            {
                int _skipRows = (PageNumber - 1) * PageSize; // if this is not the first call, need move forward
                int _totalCount = 0; // placeholder for the total amount of records

                using (EFModels.AWEntities _efEntities = new EFModels.AWEntities())
                {
                    // Using var because this is returning an anonymous type
                    var _entityrows = (from sheader in _efEntities.SalesOrderHeaders
                                       join sdetail in _efEntities.SalesOrderDetails on sheader.SalesOrderID equals sdetail.SalesOrderID
                                       join product in _efEntities.Products on sdetail.ProductID equals product.ProductID
                                       select new Models.SalesProduct()
                                       {
                                           OrderQuantity = sdetail.OrderQty,
                                           ProductId = sdetail.ProductID,
                                           ProductName = product.Name,
                                           ProductNumber = product.ProductNumber,
                                           ShipDate = sheader.ShipDate ?? DateTime.MinValue,
                                           UnitPrice = sdetail.UnitPrice,
                                           OnlineOrderFlag = sheader.OnlineOrderFlag
                                       });

                    if (ActiveOnly)
                    {
                        // Showing how to use ActiveOnly without a boolean flag as an example
                        _entityrows = _entityrows.Where(er => er.ShipDate.Year > 2013);
                    }

                    if (!string.IsNullOrWhiteSpace(SearchFilter))
                    {
                        // This can be customized for each implementation
                        bool _onlineOrder;
                        bool.TryParse(SearchFilter, out _onlineOrder);
                        _entityrows = _entityrows.Where(f => f.OnlineOrderFlag == _onlineOrder);
                    }

                    if (!string.IsNullOrWhiteSpace(SearchTerm))
                    {
                        // This can be customized for each implementation
                        _entityrows = _entityrows.Where(f => f.ProductName.Contains(SearchTerm.Trim()));
                    }

                    // Getting count will execute a SELECT COUNT(*)
                    // Like to do this before adding sort criteria
                    _totalCount = _entityrows.Count();

                    if (!string.IsNullOrWhiteSpace(SortExpression))
                    {
                        bool IsSortDESC = false;
                        if (SortOrder.ToLower() == "asc") { IsSortDESC = true; }

                        _entityrows = _entityrows.OrderBy<Models.SalesProduct>(SortExpression, IsSortDESC);
                    }

                    _entityrows = _entityrows.Skip(_skipRows).Take(PageSize);

                    return new PagedResult<Models.SalesProduct>(_entityrows.ToList(), _totalCount);


                }
            }
            catch (Exception)
            {
                throw;
            }
        }

If you’re viewing this as part of the Paging series, continue with that series here: Business Objects for Paging

Paging with Entity Framework (Simple)

Whenever you have a need to show a paged list of your items, it is a must that this is done in your repository in Entity Framework so that you’re not passing around more data than is necessary. There are too many examples of paging on the web which show paging at the UI level, especially terrible ones are showing doing a LINQ to SQL query with ToList() after it. Horrible!

DON’T EVER DO THIS!

var _result = dbContext.Customers.ToList().Skip(page).Take(pageSize);

The following example is only called “Simple” because it addresses one table for paging. This will often be the case, but you can view a more complicated query here: Paging with Entity Framework (Advanced)

The example below accepts all the common parameters and handles assembling a Linq query. It DOES NOT execute the query until the entire statement is built. There is a necessary evil call to get the count, once the WHERE clause criteria is completed. You’ll notice that this call is done before adding the OrderBy so as not to burden the Count query with sorting.

The OrderBy statement is not standard in Linq. It uses an extension method that you can take a look at here: OrderBy Extension for Linq Queries

You’ll also notice that this method returns a PagedResult object. This is just a custom DTO that brings back a strongly typed list and a total count. You can see the code for this object here. PagedResult for Paging

This LINQ code was pulled directly from the LookupsRep class in the YTG MVC Lookups and Paging Demo Download.

/// <summary>
/// Simple Paged results from multiple tables in EF Query
/// Yasgar Technology Group, Inc. - www.ytgi.com
/// </summary>
/// <param name="Page"></param>
/// <param name="PageSize"></param>
/// <param name="SearchTerm"></param>
/// <param name="SearchFilter"></param>
/// <param name="SortColumn"></param>
/// <param name="SortOrder"></param>
/// <param name="ActiveOnly"></param>
/// <returns></returns>
public async Task<PagedResult<LuCategories>> GetLuCategoriesByEFAsync(int Page,
            int PageSize,
            string SearchTerm,
            string SearchFilter,
            string SortColumn,
            string SortOrder,
            bool ActiveOnly)
{
    try
    {
        int _skipRows = (Page - 1) * PageSize; // if this is not the first call, need move forward
        int _totalCount = 0; // placeholder for the total amount of records

        // Using var because this is returning an anonymous type
        var _entityrows = (from item in LuContext.LuCategories.Include(a => a.LuItems)
                           select item);

        if (ActiveOnly)
        {
            // Showing how to use ActiveOnly without a boolean flag as an example
            _entityrows = _entityrows.Where(er => er.IsActive == true);
        }

        if (!string.IsNullOrWhiteSpace(SearchFilter))
        {
            // This can be customized for each implementation
            _entityrows = _entityrows.Where(f => f.ShortName == SearchFilter);
        }

        if (!string.IsNullOrWhiteSpace(SearchTerm))
        {
            // This can be customized for each implementation
            _entityrows = _entityrows.Where(f => f.Name.Contains(SearchTerm.Trim()));
        }

        // Getting count will execute a SELECT COUNT(*)
        // Like to do this before adding sort criteria
        _totalCount = _entityrows.Count();

        if (!string.IsNullOrWhiteSpace(SortColumn))
        {
            bool IsSortDESC = false;
            if (SortOrder.ToLower() == "desc") { IsSortDESC = true; }

            _entityrows = _entityrows.OrderBy(SortColumn, IsSortDESC);
        }

        _entityrows = _entityrows.Skip(_skipRows).Take(PageSize);

        return new PagedResult<Models.LuCategories>(await _entityrows.ToListAsync(), _totalCount);

    }
    catch (Exception)
    {
        throw;
    }
}

If you’re viewing this as part of the Paging series, continue with that series here: Business Objects for Paging

Linq OrderBy Extension using a string

When writing more complicated Linq queries against an ORM, it is often desirable to be able to randomly pass in a field name as a string and sort order (direction) on the fly. This comes in especially handy for the paging project that you’ll also find on this Blog. I developed this Linq OrderBy Extension using a string and boolean value to determine if should sort ascending or descending.

Basically, with out of the box Linq, you can’t easily change the sort column dynamically. There is a Dynamic Linq Library (System.Linq.Dynamic) that you can install using a NuGet package, but that seemed overkill to me for this need. After looking that over, if you feel it’s a better solution for you, you won’t need to use this extension.

This extension method allows you to pass in the collection as IQueryable, so you can pass in Linq queries that haven’t been executed yet. Then it accepts your OrderByProperty and whether you want the query set to sort the results Descending as a boolean value.

You implement it like this:

using (EFModels.AWEntities _efEntities = new EFModels.AWEntities())
{
    var _entityrows = (from sheader in _efEntities.SalesOrderHeaders
                       select sheader);

    if (!string.IsNullOrWhiteSpace(SortExpression))
    {
        bool IsSortDESC = false;
        if (SortOrder.ToLower() == "asc") { IsSortDESC = true; }
  
        _entityrows = _entityrows.OrderBy(SortExpression, IsSortDESC);
    }

	return _entityrows.ToList();
	
}

Put the following Extension in your extensions class and make sure to put a using to that location at the top of the class where you’re going to use it.

    /// <summary>
    /// Implementation of OrderBy Extension Method for IQueryable Collections
    /// </summary>
    /// <typeparam name="TEntity">Generic Type Object</typeparam>
    /// <param name="p_Source">The collection to order</param>
    /// <param name="p_OrderByProperty">The property to order by</param>
    /// <param name="p_Descending">True to sort Descending</param>
    /// <returns></returns>
    public static IQueryable<TEntity> OrderBy<TEntity>(this IQueryable<TEntity> p_Source, string p_OrderByProperty, bool p_Descending)
    {
        try
        {
            string command = p_Descending ? "OrderByDescending" : "OrderBy";
            var type = typeof(TEntity);
            var property = type.GetProperty(p_OrderByProperty);
            var parameter = Expression.Parameter(type, "p");
            var propertyAccess = Expression.MakeMemberAccess(parameter, property);
            var orderByExpression = Expression.Lambda(propertyAccess, parameter);
            var resultExpression = Expression.Call(typeof(Queryable), command, new Type[] { type, property.PropertyType },
                                          p_Source.Expression, Expression.Quote(orderByExpression));
            return p_Source.Provider.CreateQuery<TEntity>(resultExpression);
        }
        catch (ArgumentNullException)
        {
            throw new Exception("The OrderByProperty value of: '" + p_OrderByProperty + "', was empty or is not a proper column name!");
        }
        catch (Exception)
        {
            throw;
        }
    }

If you like this, take a look at some other extension methods that I use all the time:

Jack’s Top 10 String Extension Methods
Trim String to Particular Length

Display SQL for LINQ to Entities

If you’re fluent in SQL and want to display SQL for LINQ to Entities in your .NET application, there are ways to do it. It heavily depends on the version of Entity Framework you’re using

I was looking for a way to display the SQL behind a LINQ to entities query in the immediate window. Lots of sites have some code, except they didn’t seem to work for me ☹.

In Entity Framework version 6.0, you can use the following code. You have to make sure that you don’t have your LINQ query ending with anything that will cause the query to execute, such as .ToList().

((System.Linq.IQueryable) %queryvar%).ToString()

You can use this right in the immediate window. Here is another that I found on the web that didn’t work for me for whatever reason. I think ObjectQuery is not in that library any longer:

((System.Data.Objects.ObjectQuery) %queryvar%).ToTraceString()

If you don’t mind changing your code, there is a way to display the query in the Output windows (after it executes) with this code Added before your SaveChanges().

context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);

For Entity Framework Core, version 6.x, they have added a DebugView property in Visual Studio so that you can inspect the query at a stop point. If you want to get the query in code, you can use this:

string _myQuery = _entityrows.ToQueryString();

Keep on LINQing!

Saving Child Objects in Entity Framework

Trying to work with Entity Framework in a normalized database can pose a little learning curve as the the intricacies of the ORM.

Trying to work with Entity Framework in a normalized database can pose a little learning curve as due to the intricacies of the ORM. As an example, most of my business objects have child collections, as I assume yours do as well. A user has phones, addresses, orders etc. When I call up a user, I usually want all these sub collections as well.

In most cases, my applications have a web service in between the repository and the user interface. This means that I’m working detached from the ORM. Usually having the Entities mapped to more well structured business objects in the Service layer as well.

So what happens when I call up a user and we want to modify an address, or add a new address? Well, let’s take a look.

First, I have a database with two tables that both have Primary Keys and a Foreign Key relationship.

The Address.CustId in the Addresses table is linked to the Customer.Id primary key. If I run a query on the database as below, you can see the data:

SELECT c.Id as CustID, c.Name, a.Id as AddId
		, a.Address1, a.CustId
FROM [lookupsdemo].[dbo].[Customers] c
	JOIN [lookupsdemo].[dbo].[Addresses] a
ON c.Id = a.CustId

The data this produces is:

So now let’s say I want to update an address. According the Microsoft, this is easy, just write some code like:

Models.Customer _cust = new Models.Customer() { Id = 2, Name = "Jim Beam" };
Models.Address _add = new Models.Address() { Id = 2, Address1 = "1601 Penn Ave", CustId = 2 };
_cust.Addresses.Add(_add);

Models.lookupsdemoEntities _entities = new Models.lookupsdemoEntities();
_entities.Customers.Attach(_cust);
_entities.Entry(_cust).State = System.Data.Entity.EntityState.Modified;
_entities.SaveChanges();

Life should be good, after running this code. The data looks like:

What? Looks like the name was updated, but not the address. Well, it turns out that marking the parent object as EntityState.Modified does not have any effect on the child objects.

Let’s modify the code a bit and see what happens:

Models.Customer _cust = new Models.Customer() { Id = 2, Name = "Jim Beam 2" };
Models.Address _add = new Models.Address() { Id = 2, Address1 = "1602 Penn Ave", CustId = 2 };
_cust.Addresses.Add(_add);

Models.lookupsdemoEntities _entities = new Models.lookupsdemoEntities();
_entities.Customers.Attach(_cust);
_entities.Entry(_cust).State = System.Data.Entity.EntityState.Modified;

foreach(Models.Address _item in _cust.Addresses)
{
	_entities.Addresses.Attach(_item);
	_entities.Entry(_item).State = System.Data.Entity.EntityState.Modified;
}

_entities.SaveChanges();

Alright, after running this code, let’s see what we have:

Well now, we finally got what we wanted, Jim’s name and Address1 have been updated!

What if we went to add a new address you ask. Well, just for fun, I threw in the code like this:

Models.Customer _cust = new Models.Customer() { Id = 2, Name = "Jim Beam 2" };
Models.Address _add = new Models.Address() { Id = 2, Address1 = "1602 Penn Ave", CustId = 2 };
Models.Address _add1 = new Models.Address() { Id = 0, Address1 = "1400 Fun Street", CustId = 2 };
_cust.Addresses.Add(_add);
_cust.Addresses.Add(_add1);

Models.lookupsdemoEntities _entities = new Models.lookupsdemoEntities();
_entities.Customers.Attach(_cust);
_entities.Entry(_cust).State = System.Data.Entity.EntityState.Modified;

foreach(Models.Address _item in _cust.Addresses)
{
	_entities.Addresses.Attach(_item);
	_entities.Entry(_item).State = System.Data.Entity.EntityState.Modified;
}

_entities.SaveChanges();

Anybody want to bet a dollar on whether this worked? Well, you’re lucky, because you would have lost your dollar. This results in a System.Data.Entity.Infrastructure.DbUpdateConcurrencyException.

I’ve modified the code to use a marker to tell if the child collection items are modifications or additions, here I’m using the Id > 0 to assume these are mods, all others are adds.

Models.Customer _cust = new Models.Customer() { Id = 2, Name = "Jim Beam 3" };
Models.Address _add = new Models.Address() { Id = 2, Address1 = "1603 Penn Ave", CustId = 2 };
Models.Address _add1 = new Models.Address() { Id = 0, Address1 = "1400 Fun Street", CustId = 2 };
_cust.Addresses.Add(_add);
_cust.Addresses.Add(_add1);

Models.lookupsdemoEntities _entities = new Models.lookupsdemoEntities();
_entities.Customers.Attach(_cust);
_entities.Entry(_cust).State = System.Data.Entity.EntityState.Modified;

foreach(Models.Address _item in _cust.Addresses)
{
	if (_item.Id > 0)
	{
		_entities.Addresses.Attach(_item);
		_entities.Entry(_item).State = System.Data.Entity.EntityState.Modified;
	}
	else
	{
		_entities.Addresses.Add(_item);
	}
}

_entities.SaveChanges();

This gets us everything we wanted. We get the parent modifications, as well as the address modification and the new address.

As you can see that Jim is happy with both of his addresses.

It’s a little more code than I would like to use, but in the long run, it’s still less than coding a SQLClient Command with stored procedures.