blocks|key|442565|text|尝尝这个|type|unstyled|depth|inlineStyleRanges|entityRanges|data|442566|++var+progy+=+(
+++++++++from+u+in+db.ProgramLocations+join+b+in+db.Programs
+++++++++on+u.ProgramID+equals+b.ProgramID+into+yG+
+++++++++from+y1+in+yG.DefaultIfEmpty()+
+++++++++where+y1+==+null
+++++++++select+u.ProgramID
++++++++).ToList();|code-block|syntax|javascript|442567|你可以查看this+post+on+MSDN。|offset|length|442568|希望这对你有用。|442569|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/bb397895.aspx^0|0|0|5|H|0|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@$I|X|J|Y|1|Z]]|A|$]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

Try this

<pre><code> var progy = (
 from u in db.ProgramLocations join b in db.Programs
 on u.ProgramID equals b.ProgramID into yG 
 from y1 in yG.DefaultIfEmpty() 
 where y1 == null
 select u.ProgramID
 ).ToList();
</code></pre>

You can check <a href="http://msdn.microsoft.com/en-us/library/bb397895.aspx" rel="nofollow">this post on MSDN</a>.

Hope this works for you.

blocks|key|657068|text|你能用except代替吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|657069|var+progy+=+(
++from+u+in+db.ProgramLocations
++select+u.ProgramID
).Except(from+b+in+db.Programs+select+b.ProgramID);|code-block|syntax|javascript|657070|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Could you use except instead?

<pre><code>var progy = (
 from u in db.ProgramLocations
 select u.ProgramID
).Except(from b in db.Programs select b.ProgramID);
</code></pre>

blocks|key|442830|text|根据this+question，您想要使用.DefaultIfEmpty。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|442831|var+query+=+from+p+in+Programs
++++++++++++join+pl+in+ProgramLocations
++++++++++++++++on+p.ProgramID+equals+pl.ProgramID+into+pp
++++++++++++from+pl+in+pp.DefaultIfEmpty()
++++++++++++where+pl+==+null
++++++++++++select+p;|code-block|syntax|javascript|442832|下面是一个完整的工作示例，其中包含一些模拟数据对象：|442833|using+System;
using+System.Collections.Generic;
using+System.Linq;
using+System.Text;

namespace+LinqTest
{
++++class+LinqProgram
++++{
++++++++public+class+Program
++++++++{
++++++++++++public+int+ProgramID+{+get;+set;+}
++++++++++++public+string+ProgramName+{+get;+set;+}
++++++++}

++++++++public+class+ProgramLocation
++++++++{
++++++++++++public+int+ProgramLocationID+{+get;+set;+}
++++++++++++public+int+ProgramID+{+get;+set;+}
++++++++++++public+string+ProgramLocationName+{+get;+set;+}
++++++++}

++++++++public+static+List<Program>+Programs+=+new+List<Program>();
++++++++public+static+List<ProgramLocation>+ProgramLocations+=+new+List<ProgramLocation>();

++++++++static+void+Main(string[]+args)
++++++++{
++++++++++++FillTestData();

++++++++++++var+query+=+from+p+in+Programs
++++++++++++++++++++++++join+pl+in+ProgramLocations
++++++++++++++++++++++++++++on+p.ProgramID+equals+pl.ProgramID+into+pp
++++++++++++++++++++++++from+pl+in+pp.DefaultIfEmpty()
++++++++++++++++++++++++where+pl+==+null
++++++++++++++++++++++++select+p;

++++++++++++foreach+(var+r+in+query)
++++++++++++{
++++++++++++++++Console.WriteLine("{0}:+{1}",+r.ProgramID,+r.ProgramName);
++++++++++++}

++++++++++++Console.ReadLine();
++++++++}

++++++++private+static+void+FillTestData()
++++++++{
++++++++++++var+p+=+new+Program()
++++++++++++{
++++++++++++++++ProgramID+=+Programs.Count+%2B+1,
++++++++++++++++ProgramName+=+"Scary+Lesson"
++++++++++++};
++++++++++++var+pl+=+new+ProgramLocation()
++++++++++++{
++++++++++++++++ProgramLocationID+=+ProgramLocations.Count+%2B+1,
++++++++++++++++ProgramID+=+p.ProgramID,
++++++++++++++++ProgramLocationName+=+"Haunted+House"
++++++++++++};
++++++++++++Programs.Add(p);
++++++++++++ProgramLocations.Add(pl);

++++++++++++p+=+new+Program()
++++++++++++{
++++++++++++++++ProgramID+=+Programs.Count+%2B+1,
++++++++++++++++ProgramName+=+"Terrifying+Teachings"
++++++++++++};

++++++++++++pl+=+new+ProgramLocation()
++++++++++++{
++++++++++++++++ProgramLocationID+=+ProgramLocations.Count+%2B+1,
++++++++++++++++ProgramID+=+p.ProgramID,
++++++++++++++++ProgramLocationName+=+"Mystical+Mansion"
++++++++++++};
++++++++++++Programs.Add(p);
++++++++++++ProgramLocations.Add(pl);

++++++++++++p+=+new+Program()
++++++++++++{
++++++++++++++++ProgramID+=+Programs.Count+%2B+1,
++++++++++++++++ProgramName+=+"Unassociated+Program"
++++++++++++};
++++++++++++Programs.Add(p);
++++++++}
++++}
}|442834|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join^0|L|F|2|D|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@$9|Z|A|10|1|11]]|E|$]]|$1|F|3|G|5|H|7|12|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|13|8|@]|D|@]|E|$]]|$1|M|3|N|5|H|7|14|8|@]|D|@]|E|$I|J]]|$1|O|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|P|$Q|$5|R|S|T|E|$U|V]]]]

You want to use <code>.DefaultIfEmpty</code>, as per <a href="https://stackoverflow.com/questions/700523/linq-to-sql-left-outer-join">this question</a>.

<pre><code>var query = from p in Programs
 join pl in ProgramLocations
 on p.ProgramID equals pl.ProgramID into pp
 from pl in pp.DefaultIfEmpty()
 where pl == null
 select p;
</code></pre>

Here's a full, working example with some mock data objects:

<pre><code>using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LinqTest
{
 class LinqProgram
 {
 public class Program
 {
 public int ProgramID { get; set; }
 public string ProgramName { get; set; }
 }

 public class ProgramLocation
 {
 public int ProgramLocationID { get; set; }
 public int ProgramID { get; set; }
 public string ProgramLocationName { get; set; }
 }

 public static List&lt;Program&gt; Programs = new List&lt;Program&gt;();
 public static List&lt;ProgramLocation&gt; ProgramLocations = new List&lt;ProgramLocation&gt;();

 static void Main(string[] args)
 {
 FillTestData();

 var query = from p in Programs
 join pl in ProgramLocations
 on p.ProgramID equals pl.ProgramID into pp
 from pl in pp.DefaultIfEmpty()
 where pl == null
 select p;

 foreach (var r in query)
 {
 Console.WriteLine("{0}: {1}", r.ProgramID, r.ProgramName);
 }

 Console.ReadLine();
 }

 private static void FillTestData()
 {
 var p = new Program()
 {
 ProgramID = Programs.Count + 1,
 ProgramName = "Scary Lesson"
 };
 var pl = new ProgramLocation()
 {
 ProgramLocationID = ProgramLocations.Count + 1,
 ProgramID = p.ProgramID,
 ProgramLocationName = "Haunted House"
 };
 Programs.Add(p);
 ProgramLocations.Add(pl);

 p = new Program()
 {
 ProgramID = Programs.Count + 1,
 ProgramName = "Terrifying Teachings"
 };

 pl = new ProgramLocation()
 {
 ProgramLocationID = ProgramLocations.Count + 1,
 ProgramID = p.ProgramID,
 ProgramLocationName = "Mystical Mansion"
 };
 Programs.Add(p);
 ProgramLocations.Add(pl);

 p = new Program()
 {
 ProgramID = Programs.Count + 1,
 ProgramName = "Unassociated Program"
 };
 Programs.Add(p);
 }
 }
}
</code></pre>

blocks|key|442862|text|SELECT+pfa.PetID,+pt.PetTypeDesc,+pfa.petname,+pf.PetOwner,+pf.remarks,+pat.AdoptedBy
++++FROM+dbo.PetForAdoption+pfa
++++JOIN+dbo.PetAdoptionTran+pat
++++ON+pfa.PetID+=+pat.PetID
++++JOIN+dbo.PetTypes+pt+
++++ON+pfa.PetTypeID+=+pt.PetTypeID
++++JOIN+dbo.PetProfile+pf
++++ON+pfa.PetID+=+pf.PetID
++++ORDER+BY+pt.PetTypeDesc|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|442863|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT pfa.PetID, pt.PetTypeDesc, pfa.petname, pf.PetOwner, pf.remarks, pat.AdoptedBy
 FROM dbo.PetForAdoption pfa
 JOIN dbo.PetAdoptionTran pat
 ON pfa.PetID = pat.PetID
 JOIN dbo.PetTypes pt 
 ON pfa.PetTypeID = pt.PetTypeID
 JOIN dbo.PetProfile pf
 ON pfa.PetID = pf.PetID
 ORDER BY pt.PetTypeDesc
</code></pre>

blocks|key|442936|text|我现在有了这个问题，整洁的SQL是必须的，并以一种非常优化的方式使其工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|442937|Linq：|442938|var+recs=from+programs+in+db.Programs
+++++++++from+locations+in+db.ProgramLocations.where(x=>+x.ProgramID+==+programs.ProgramID).DefaultIfEmpty()
+++++++++where+locations.ProgramID+==+null++//Compiler+creates+a+warning+because+int+will+never+be+null,+but+it+works+and+creates+outer+left+join
+++++++++select+programs.ProgramID;|code-block|syntax|javascript|442939|结果SQL：|442940|SELECT+
"Extent1"."ProgramID"+AS+"ProgramID"
FROM+"DBO"."Program"+"Extent1"
LEFT+OUTER+JOIN+"DBO"."ProgramLocation"+"Extent2"+ON+"Extent2"."ProgramID"+=+"Extent1"."ProgramID"
WHERE+("Extent2"."ProgramID"+IS+NULL)|442941|如果您有多个连接条件，并且还适用于多个左连接，则此方法也适用|442942|ps。我在Oracle中使用EF，所以不确定这在E的SQLServer提供程序上是否会略有不同|442943|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|U|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|V|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|W|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

I had this issue now, and neat SQL is a must and got it working in a very optimised way.
Linq:
<pre><code>var recs=from programs in db.Programs
 from locations in db.ProgramLocations.where(x=&gt; x.ProgramID == programs.ProgramID).DefaultIfEmpty()
 where locations.ProgramID == null //Compiler creates a warning because int will never be null, but it works and creates outer left join
 select programs.ProgramID;
</code></pre>
Resulting SQL:
<pre><code>SELECT 
&quot;Extent1&quot;.&quot;ProgramID&quot; AS &quot;ProgramID&quot;
FROM &quot;DBO&quot;.&quot;Program&quot; &quot;Extent1&quot;
LEFT OUTER JOIN &quot;DBO&quot;.&quot;ProgramLocation&quot; &quot;Extent2&quot; ON &quot;Extent2&quot;.&quot;ProgramID&quot; = &quot;Extent1&quot;.&quot;ProgramID&quot;
WHERE (&quot;Extent2&quot;.&quot;ProgramID&quot; IS NULL)
</code></pre>
This also work if you have more than one join condition And also work for more than one left join
ps. I am using EF with Oracle, so not sure if this will be slightly different on the SQLServer provider for E

How can I convert properly this SQL to linq

<pre><code>select t1.ProgramID
from Program t1 LEFT JOIN ProgramLocation t2 ON t1.ProgramID = t2.ProgramID 
where t2.ProgramID IS NULL
</code></pre>

I try that but it not working 

<pre><code>var progy = (
 from u in db.ProgramLocations join b in db.Programs
 on u.ProgramID equals b.ProgramID into yG 
 from y1 in yG.DefaultIfEmpty() 
 where u.ProgramID == null
 where u.ProgramID == null 
 select u.ProgramID
 ).ToList();
</code></pre>

THANKS

Convert SQL to Linq left join with null

如何正确地将此SQL转换为linqselect  t1.ProgramIDfrom Program t1 LEFT JOIN ProgramLocation t2 ON  t1.ProgramID = t2.ProgramID where t2.ProgramID IS NULL我试过了，但不起作用var progy ...

问使用null将SQL转换为Linq左连接
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用null将SQL转换为Linq左连接EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用null将SQL转换为Linq左连接
EN