CODEWITHSUNDEEP
c#linq
ADIL HASSAN
ADIL HASSAN

Reputation: 25

SQL to LINQ unable to convert SQL join query with multiple conditions with "and"

I was trying to convert a SQL Server query with multiple table joins and multiple conditions in a join to LINQ. I am getting an error while trying to use multiple conditions in join using &&.

Below is my SQL Server query.

SELECT j.ID,
       j.OnTime AS 'Alarm Appear Time',
       j.OffTime AS 'Alarm Disappear Time',
       j.OnNoticeTime AS 'Alarm Ack Time',
       j.OffNoticeTime AS 'Alarm Close Time',
       j.InstanceID AS 'Asset ID',
       i.RemarkPath AS 'Asset',
       j.PropertyID AS 'Message ID',
       p.Remark AS 'Alarm Message',
       a.X4AlarmGroup_ID AS 'Priority ID',
       g.Name AS 'Priority Group'
FROM [XAMRuntimeX4].[dbo].[X4Journals] j
    JOIN [XAMRuntimeX4].[dbo].[V_X4InstanceRemarks] i
        ON j.InstanceID = i.Instance_ID
    JOIN [XAMRuntimeX4].[dbo].[V_AutomationControlPropertyRemarks] p
        ON j.PropertyID = p.Property_ID
    JOIN [XAMControlX4].[dbo].[X4AlarmConfigs] a
        ON j.InstanceID = a.X4Instance_ID
           AND j.PropertyID = a.X4Property_ID
    JOIN [XAMControlX4].[dbo].[X4AlarmGroups] g
        ON a.X4AlarmGroup_ID = g.ID
WHERE i.Language = 'iv'
      AND p.LANGUAGE = 'iv'
ORDER BY j.OnTime DESC;

Below given is my attempt to convert it into LINQ

var finalAlarm = xAMRuntimeX4Context.X4Journals
    .Join(xAMControlX4Context.X4InstanceRemarks,
          j => j.InstanceID, i => i.Instance_ID, (j, i) => new { j, i })
    .Join(xAMControlX4Context.AutomationControlPropertyRemarks,
          ppc => ppc.j.PropertyID, p => p.Property_ID, (ppc, p) => new { ppc, p })
    .Join(xAMControlX4Context.X4AlarmConfigs,
          (ppc2 => ppc2.ppc.j.InstanceID, a => a.X4Instance_ID) &&
          (ppc2 => ppc2.ppc.j.PropertyID, a => a.X4Property_ID),
          (ppc2, a) => new { ppc2, a });

Upvotes: 0

Views: 85

Answers (3)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112372

Remarks

  • Your LINQ attempt is missing the X4AlarmGroups.
  • To join X4Journals and X4AlarmConfigs on two columns I used ValueTuples.
  • I also unwrapped the intermediate anonymous types.
var finalAlarm = xAMRuntimeX4Context.X4Journals
    .Join(xAMControlX4Context.X4InstanceRemarks,
            j => j.InstanceID, i => i.Instance_ID, (j, i) => new { j, i })
    .Join(xAMControlX4Context.AutomationControlPropertyRemarks,
            ppc => ppc.j.PropertyID, p => p.Property_ID, (ppc, p) => new { ppc.j, ppc.i, p })
    .Join(xAMControlX4Context.X4AlarmConfigs,
            ppc2 => new { k1 = ppc2.j.InstanceID, k2 = ppc2.j.PropertyID },
               a => new { k1 = a.X4Instance_ID,   k2 = a.X4Property_ID },
            (ppc2, a) => new { ppc2.j, ppc2.i, ppc2.p, a })
    .Join(xAMControlX4Context.X4AlarmGroups,
            ppc3 => ppc3.a.X4AlarmGroup_ID, g => g.ID, (ppc3, g) => new { ppc3.j, ppc3.i, ppc3.p, ppc3.a, g })
    .Where(ppc4 => ppc4.i.Language == "iv" && ppc4.p.Language == "iv")
.OrderBy(ppc4 => ppc4.j.OnTime)
.Select(x => new {
    x.j.ID,
    AlarmAppearTime = x.j.OnTime,
    AlarmDisappearTime = x.j.OffTime,
    AlarmAckTime = x.j.OnNoticeTime,
    AlarmCloseTime = x.j.OffNoticeTime,
    AssetID = x.j.InstanceID,
    Asset = x.i.RemarkPath,
    MessageID = x.j.PropertyID,
    AlarmMessage = x.p.Remark,
    PriorityID = x.a.X4AlarmGroup_ID,
    PriorityGroup = x.g.Name
});

Upvotes: 1

Cetin Basoz
Cetin Basoz

Reputation: 23797

In Linq To SQL, thinking you have proper relations at database level, you seldom need joins. Instead you use navigational properties. In your case, it is not clear if those relations are 1-to-1, 1-to-many, many-to-1 or many-to-many. It would matter just for getting the Remarkpath, Remark like properties. Looking from your SQL, as it stands, we have to consider that they may lead to multiple rows, thus it translates to LinqToSQL as:

var finalAlarm = from j in xAMRuntimeX4Context.X4Journals
                 from i in j.X4InstanceRemarks.Where(x => x.Langauge == "iv")
                 from p in j.AutomationControlPropertyRemarks.Where(x => x.Langauge == "iv")
                 from a in j.X4AlarmConfigs
                 from g in a.X4AlarmGroups
                 select new
    {
        ID = j.ID,
        AlarmAppearTime = j.OnTime,
        AlarmDisappearTime = j.OffTime,
        AlarmAckTime = j.OnNoticeTime,
        AlarmCloseTime = j.OffNoticeTime,
        AssetID = j.InstanceID,
        Asset = i.RemarkPath,
        MessageId = j.PropertyId,
        AlarmMessage = p.Remark,
        PriorityID = a.X4AlarmGroup_ID,
        PriorityGroup = g.Name
    };

Note: You can use LinqPad to write this easier. Connect to your database, and check the schema. It would show you the navigations, 1-to-many etc relations.

Upvotes: 0

T N
T N

Reputation: 10024

See How to do joins in LINQ on multiple fields in single join.

You cannot use the && operator to select multiple columns in a Linq method syntax join. Instead you need to code your outer and inner key selectors to each return both key values together in a new anonymous object.

In your case, something like:

    .Join(xAMControlX4Context.X4AlarmConfigs,
          ppc2 => new { ppc2.ppc.j.InstanceID, ppc2.ppc.j.PropertyID },
          a => new { a.X4Instance_ID, a.X4Property_ID },
          (ppc2, a) => new { ppc2, a })

Upvotes: 0

Related Questions