将SQL CASE WHEN语句转换为C# [英] Converting SQL CASE WHEN statement into C#

问题描述

我正处于夏季编程实习的第一周，并且承担了使用 LINQPad <将SQL Server 2008语句转换为C#的任务. /a>.一旦完成并使其正常工作，就可以将其输入VS并根据需要进行调整.

I am on my first week of a summer programming internship and have been tasked with converting SQL Server 2008 statements into C# using LINQPad. Once I do that and get it working properly, I can enter it into VS and tweak as needed.

问题是我在运行SQL语句时不知道如何转换.在我的C#类中，我们没有将SQL语句转换为C#，而是创建了一个字符串变量，将SQL语句分配给变量，然后将变量分配给OleDbCommand对象.我的导师请了一天假，度过了漫长的周末，我几乎独自一人，对如何做和需要帮助一无所知.有很多这样的人，如果我能看到如何做的话，我可以弄清楚其余的人.这是SQL语句:

The problem is that I am running across SQL statements I have no idea how to convert. In my C# class we did not convert SQL statements into C#, we just created a string variable, assigned the SQL statement to variable and then assigned the variable to an OleDbCommand object. My mentor took the day off for the long weekend and I'm pretty much on my own and clueless on what to do and need some help. There are a couple of dozen like this one and if I can see how to do one I can figure the rest out. Here is the SQL statement:

,[hrs].{Hours] - SUM(
  CASE 
    WHEN [UnitState].[UnitStateTye] <> 'ACTIVE' 
      THEN [Allocation].[AllocatedEnergyMwh] 
    ELSE 0 
  END 
/ CAST([Unit].[NetDependableCapacity] AS FLOAT)) AS SH

我很确定这是一条if语句，类似于:

I'm pretty sure this is an if statement along the lines of:

if [UnitState].[UnitStateType] does not equal active 
then SH equals [hrs].[Hours] minus the the sum of
  [Allocation].[AllocatedEnergyMwh] / (float)[Unit].[NetDependableCapacity].  
else 
  SH = [hrs].[Hours]

我尝试了以下代码，我认为这行不通，但需要从某个地方开始:

I tried the following code, which I figured wasn't going to work but needed to start somewhere:

var results = 

    (from v in VDimUnit     
        join vf in VFactEnergyAllocation on v.UnitKey equals vf.UnitKey
        join vd in VDimGadsEvent on vf.GadsEventKey equals vd.GadsEventKey
        join vt in VDimTime on vf.TimeKey equals vt.TimeKey 
        join vus in VDimUnitState on vf.UnitKey equals vus.UnitKey
    where vd.GadsEventEndTime.Year == 2011 && v.UnitId == "THL3"
    group vf by new {vus.UnitStateType, vf.AllocatedEnergyMwh v.NetDependableCapacity,
        vt.QuarterNum} into groupItem       
    select new {groupItem.Key.QuarterNum, SUM1 = groupItem.Sum(x=> (float)x.AllocatedEnergyMwh /
        groupItem.Key.NetDependableCapacity)}).ToArray();

var resultHours = 
    (from v in VDimTime
    group v by v.QuarterNum into groupItem
    select new {Hours = groupItem.Count(), groupItem.Key}).ToDictionary(x=> x.Key, x=> x.Hours);

var finalResults = 
    (from r in results
    select new {SH_IF_NOT_ACTIVE = resultHours[r.QuarterNum] - r.SUM1,
        Hours = 
        SH_IF_ACTIVE = resultHours[r.QuarterNum]});

    if (SH != "ACTIVE")
    { 
        SH_IF_NOT_ACTIVE;
    }
    else
    {
        SH_IF_ACTIVE;
    }   

我很高兴有人可以指出我正确的方向.

I would appreciate it is someone could point me in the right direction.

推荐答案

类似以下内容:

Hours = condition ? code when true : code when false;

这篇关于将SQL CASE WHEN语句转换为C#的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！