An optimized stored procedure to replace this LINQ statement
I have the following two tables in SQL Server 2008
TABLE [JobUnit](
[idJobUnit] [int] IDENTITY(1,1) NOT NULL,
[Job_idJob] [int] NOT NULL, // Foreign key here
[UnitStatu开发者_运维知识库s] [tinyint] NOT NULL, // can be (0 for unprocessed, 1 for processing, 2 for processed)
)
TABLE [Job](
[idJob] [int] IDENTITY(1,1) NOT NULL,
[JobName] [varchar(50)] NOT NULL,
)
Job : JobUnit is one-to-many relationship
I am trying to write an efficient store procedure that would replace the following LINQ statement
public enum UnitStatus{
unprocessed,
processing,
processed,
}
int jobId = 10;
using(EntityFramework context = new EntityFramework())
{
if (context.JobUnits.Where(ju => ju.Job_idJob == jobId)
.Any(ju => ju.UnitStatus == (byte)UnitStatus.unproccessed))
{
// Some JobUnit is unprocessed
return 1;
}
else
{
// There is no unprocessed JobUnit
if (context.JobUnits.Where(ju => ju.Job_idJob == jobId) //
.Any(ju => ju.UnitStatus == (byte)UnitStatus.processing))
{
// JobUnit has some unit that is processing, but none is unprocessed
return 2;
}
else
{
// Every JoUnit is processed
return 3;
}
}
}
Thanks for reading
So really, you're just looking for the lowest state of all the units in a particular job?
CREATE PROCEDURE GetJobState @jobId int AS
SELECT MIN(UnitStatus)
FROM JobUnit
WHERE Job_idJob = @jobId
I should also say you could use this approach just as easly in Linq.
精彩评论