title: How to add array element check (IN SQL operation) in left outer join?
body: <p>I have this SQL Query</p>
  <pre><code>SELECT [c].[ClinicID], [c0].[ClinicNotificationId], [c0].[AccountRoleMemberId], [c0].[ClinicId], [c0].[DoctorId], [c0].[Email], [c0].[NotificationType], [c0].[SMS]
  FROM [Clinic] AS [c]
  LEFT JOIN [ClinicNotificationRecipient] AS [c0] ON [c].[ClinicID] = [c0].[ClinicId] and 
  [c0].[AccountRoleMemberId] IN (N'2663c1dc-b414-43a8-9a89-8ff76ebd87e1', N'34273572-b322-45c1-9c95-ce00d29e843d', N'3be573bc-499e-40ba-b245-ce7950123d25', N'6ad82ec2-f7a5-4550-84f2-c3531a2909bf', N'6dd9ac49-8c46-491a-84db-6807efd04935', N'70e47962-4509-4f70-b958-e0eaeced07a8', N'70f651fc-9df8-4342-9678-433b0e63717e', N'b469cd6e-ceb0-46f8-b70e-045c6afcb207', N'b7aa5a0b-f00d-4c28-9ba5-745dcf77135d', N'f6425f8e-f124-4e64-ba6b-ea11bfd81078', N'ff4cedad-80b3-498c-b1ff-e893a18aea09')
  WHERE (([c].[Active] = CAST(1 AS bit)) AND ([c].[DoctorID] = @__doctorid_0))
  </code></pre>
  <p>I would like to perform this query in LINQ C#. At first I tried this LINQ query:</p>
  <pre><code>var q = from c in Clinic
              join cnr in ClinicNotificationRecipient on c.ClinicID equals cnr.ClinicId into g
              from gcnr in g.DefaultIfEmpty()
              where c.Active == true &amp;&amp; c.DoctorID == doctorid &amp;&amp; (gcnr == null || subUserAcctRoleMemberIds.Contains(gcnr.AccountRoleMemberId))
              select new {
                  c.ClinicID,
                  cnr = gcnr
              };
  </code></pre>
  <p>however this gave me this sql query</p>
  <pre><code>SELECT [c].[ClinicID], [c0].[ClinicNotificationId], [c0].[AccountRoleMemberId], [c0].[ClinicId], [c0].[DoctorId], [c0].[Email], [c0].[NotificationType], [c0].[SMS]
  FROM [Clinic] AS [c]
  LEFT JOIN [ClinicNotificationRecipient] AS [c0] ON [c].[ClinicID] = [c0].[ClinicId]
  WHERE (([c].[Active] = CAST(1 AS bit)) AND ([c].[DoctorID] = @__doctorid_0)) AND (([c0].[ClinicNotificationId] IS NULL OR [c0].[AccountRoleMemberId] IS NULL) OR [c0].[AccountRoleMemberId] IN (N'2663c1dc-b414-43a8-9a89-8ff76ebd87e1', N'34273572-b322-45c1-9c95-ce00d29e843d', N'3be573bc-499e-40ba-b245-ce7950123d25', N'6ad82ec2-f7a5-4550-84f2-c3531a2909bf', N'6dd9ac49-8c46-491a-84db-6807efd04935', N'70e47962-4509-4f70-b958-e0eaeced07a8', N'70f651fc-9df8-4342-9678-433b0e63717e', N'b469cd6e-ceb0-46f8-b70e-045c6afcb207', N'b7aa5a0b-f00d-4c28-9ba5-745dcf77135d', N'f6425f8e-f124-4e64-ba6b-ea11bfd81078', N'ff4cedad-80b3-498c-b1ff-e893a18aea09'))
  </code></pre>
  <p>How can I move the <code>IN</code> condition as part of the left join operation?</p>