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:
- A FileProcess table that tracks a file (name, date, paths, app that processed it, etc.)
- 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
)
