开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜