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

Question

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 });

Please be more specific "an error"? – T N Aug 20 '22 at 15:07 — T N, Aug 20 '22 at 15:07

Olivier Jacot-Descombes · Accepted Answer · 2022-08-21T15:03:32.787

1

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
});

edited Aug 21 '22 at 15:03

answered Aug 20 '22 at 15:38

Olivier Jacot-Descombes

104,806
13
138
188

Thank You Very much. Now a small error is appearing at join of X4AlarmConfigs,with error msg "An expresssion tree may not contain a tuple literal". – ADIL HASSAN Aug 20 '22 at 19:35
@Oliver,could you please look into this tuple literal error – ADIL HASSAN Aug 21 '22 at 07:27
1

Okay I changed the tuples back to anonymous types. I used tuples because I got an error with the anonymous types, saying that `Join` cannot infer the types. The trick is to give the two compound keys the same property names (`k1`, `k2`). Now it works. – Olivier Jacot-Descombes Aug 21 '22 at 14:43
Note that with a data model having navigation properties, you can replace the complex joins by simple includes. See: [Linq: Join vs Include/ThenInclude in Entity Framework Core](https://stackoverflow.com/q/61568383/880990). – Olivier Jacot-Descombes Aug 21 '22 at 14:50
@Oliver,Excellent,superb,Thank You very much ,it worked, with anonymous types – ADIL HASSAN Aug 21 '22 at 16:52
@Oliver,now in my aspdontnet api application the error coming during runtime is " Cannot use multiple context instances within a single query execution. Ensure the query uses a single context instance" due to usage of our above query containing two db contexts – ADIL HASSAN Aug 22 '22 at 18:52

score 0 · Answer 2 · answered Aug 20 '22 at 15:37

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 })

score 0 · Answer 3 · answered Aug 20 '22 at 16:06

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.

Navigational properties are preferable, but the `V_`s are most likely views and if he can not get rid of them, he would have to stick to joins — Antonín Lejsek, Aug 20 '22 at 18:45
@AntonínLejsek, views are definitions and they are also based on tables. — Cetin Basoz, Aug 20 '22 at 21:16

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

3 Answers3