所需的LINQ查询解决方案 [英] LINQ query solution required

问题描述

我正努力获得以下LINQ解决方案.如果您能帮助我，将不胜感激.

以下是我的表结构

BatteryMaster:

I am struggling to get following LINQ solution. and will be grateful if you helps me.

Following is my table structure

BatteryMaster:

BatteryID SerialNo 
1         1111111
2         2222222
3         3333334
4         5421354
5         8975465
6         8754654
7         8746541
8         7777777
9         9987542
10        5744444
11        5687897

BatteryGroup:

BatteryGroup:

GroupID BatteryID1 BatteryID2 BatteryID3 BatteryID4 GroupName
1       1          5          6          10          Group1
2       11         2          3          4           Group2

每个电池组包含4个电池.
一旦电池成为任何组的一部分，将无法再次使用.

现在，我想编写一个LINQ查询以显示以下数据

Each battery group contains 4 battries.
Once a battery is a part of any group that cannot be used again.

Now I want to write a LINQ query to show following data

BatteryID SerialNo GroupName
1         1111111  Group1
2         2222222  Group2
3         3333334  Group2
4         5451354  Group2
5         8975465  Group1
6         8754954  Group1
7         8746541  NA
8         7777777  NA
9         9987542  NA
10        5744444  Group1
11        5687897  Group2

请不要建议我更改表结构，因为我不能这样做.

提前致谢.
〜Amol

Please don''t suggest me to change table structure because I cannot do that.

Thanks in advance.
~Amol

推荐答案

from b in Context.BatteryMaster
join c in Context.BatteryGroup on 
(b.BatteryID==c.BatteryID1 or
b.BatteryID==c.BatteryID2 or
b.BatteryID==c.BatteryID3 or
b.BatteryID==c.BatteryID4)   
select new 
{
    BatteryID = b.BatteryID,
    SerialNo = b.SerialNo,
    GroupName = c.GroupName
};

希望有帮助.

Hope this help.

非常感谢你们的辛勤劳动，
我找到了解决方案.

Thanks a lot guys for all your efforts,
I got my solution.

var BatteryInfo =
         (from objBatteryMaster in objDataContext.Battery_Masters
          join
                objVehicle in objDataContext.Vehicle_Masters on objBatteryMaster.Battery_ID equals
                objVehicle.Battery_ID
           into D_V_M
          join
               objB1 in objDataContext.Battery_Groups on objBatteryMaster.Battery_ID equals
               objB1.Battery_ID1
          into B1
          join
               objB2 in objDataContext.Battery_Groups on objBatteryMaster.Battery_ID equals
               objB2.Battery_ID2
          into B2
          join
               objB3 in objDataContext.Battery_Groups on objBatteryMaster.Battery_ID equals
               objB3.Battery_ID3
          into B3
          join
               objB4 in objDataContext.Battery_Groups on objBatteryMaster.Battery_ID equals
               objB4.Battery_ID4
          into B4
          from objVehicle in D_V_M.DefaultIfEmpty()
          from objB1 in B1.DefaultIfEmpty()
          from objB2 in B2.DefaultIfEmpty()
          from objB3 in B3.DefaultIfEmpty()
          from objB4 in B4.DefaultIfEmpty()
          orderby strSortExpression
          select new
          {
              objBatteryMaster.Battery_ID,
              objBatteryMaster.Serial_Number,
              objBatteryMaster.Purchase_Date,
              objBatteryMaster.Battery_Type.Battery_Type_Name,
              objBatteryMaster.Description,
              objBatteryMaster.Expiry_Date,
              objBatteryMaster.Battery_Type.Battery_Power,
              objVehicle.Vin_Number,
              VehicleStatus = objVehicle.Status.Status_Name,
              BatteryStatus = objBatteryMaster.Status.Status_Name,
              G1 = objB1.Battery_Group_Name,
              G2 = objB2.Battery_Group_Name,
              G3 = objB3.Battery_Group_Name,
              G4 = objB4.Battery_Group_Name,
              GroupName = objB1.Battery_Group_Name != null ? objB1.Battery_Group_Name :
                            objB2.Battery_Group_Name != null ? objB2.Battery_Group_Name :
                            objB3.Battery_Group_Name != null ? objB3.Battery_Group_Name :
                            objB4.Battery_Group_Name != null ? objB4.Battery_Group_Name : "NA"
          });

基本上，任何内部联接或外部联接语句中用or分隔的内部都不能有多个条件.

再次感谢
〜Amol

Basically you cannot have multiple conditions inside any inner join or outer join statements separated by or.

Thanks again
~Amol

这篇关于所需的LINQ查询解决方案的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！