Queryable.Join 使用内部表的 WHERE 子句？

huangapple
go评论97阅读模式
英文:

Queryable.Join with the WHERE clause using inner table?

问题

我试图使用Queryable.Join方法将对象列表连接在一起，但我想针对内部表使用WHERE子句。在这种情况下，我无法颠倒连接表的顺序。当我添加WHERE子句时，它只“看到”外部表中的字段。以下是重现我的问题的示例代码：

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  5. namespace JoinsWithIQueriable
  6. {
  7.     internal class Program
  8.     {
  9.         class Person
  10.         {
  11.             public string Name { get; set; }
  12.             public int Id { get; set; }
  13.         }
  15.         class Pet
  16.         {
  17.             public string Name { get; set; }
  18.             public int WeightInLbs { get; set; }
  19.             public Person Owner { get; set; }
  21.             public Species PetSpecies { get; set; }
  23.         }
  25.         class Species
  26.         {
  27.             public string Name { get; set; }
  28.         }
  30.         static void Main(string[] args)
  31.         {
  32.             Species dog = new Species { Name = "Dog" };
  33.             Species cat = new Species { Name = "Cat" };
  35.             Person magnus = new Person { Name = "Hedlund, Magnus", Id = 1 };
  36.             Person terry = new Person { Name = "Adams, Terry", Id = 2 };
  37.             Person charlotte = new Person { Name = "Weiss, Charlotte", Id = 3 };
  38.             Person alergictopets = new Person { Name = "Scratchy, Itchy", Id = 4 };
  40.             Pet barley = new Pet { Name = "Barley", WeightInLbs = 10, Owner = terry, PetSpecies = cat };
  41.             Pet boots = new Pet { Name = "Boots", WeightInLbs = 11, Owner = terry, PetSpecies = cat };
  42.             Pet whiskers = new Pet { Name = "Whiskers", WeightInLbs = 12, Owner = charlotte, PetSpecies = cat };
  43.             Pet daisy = new Pet { Name = "Daisy", WeightInLbs = 40, Owner = magnus, PetSpecies = dog };
  45.             List<Person> people = new List<Person> { magnus, terry, charlotte, alergictopets };
  46.             List<Pet> pets = new List<Pet> { barley, boots, whiskers, daisy };
  47.             List<Species> species = new List<Species> { dog, cat };
  49.             // 使用Queryable.Join方法将Person对象列表和Pet对象列表连接，
  50.             // 创建一个包含宠物名称和拥有宠物的人员名称的匿名类型的列表。
  51.             Console.WriteLine("query");
  53.             var query = people.AsQueryable().Join(pets,
  54.                 person => person,
  55.                 pet => pet.Owner,
  56.                 (person, pet) =>
  57.                     new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs });
  59.             foreach (var obj in query)
  60.             {
  61.                 Console.WriteLine(
  62.                     "Owner {0} - Pet {1} - Pet weight {2} ",
  63.                     obj.OwnerName,
  64.                     obj.Pet,
  65.                     obj.Weight.ToString());
  66.             }
  68.             /*
  69.                 此代码生成以下输出：
  71.                 Owner Hedlund, Magnus - Pet Daisy - Pet weight 40
  72.                 Owner Adams, Terry - Pet Barley - Pet weight 10
  73.                 Owner Adams, Terry - Pet Boots - Pet weight 11
  74.                 Owner Weiss, Charlotte - Pet Whiskers - Pet weight 12
  75.             */
  77.             Console.WriteLine("querywithwhere");
  79.             // 想要筛选体重小于12磅的宠物
  80.             var querywithwhere = people.AsQueryable()
  81.                 .Join(pets,
  82.                 person => person,
  83.                 pet => pet.Owner,
  84.                 (person, pet) =>
  85.                     new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs });
  87.             foreach (var obj in querywithwhere)
  88.             {
  89.                 Console.WriteLine(
  90.                     "Owner {0} - Pet {1} - Pet weight {2} ",
  91.                     obj.OwnerName,
  92.                     obj.Pet,
  93.                     obj.Weight.ToString());
  94.             }
  96.             Console.WriteLine("Press any key to continue...");
  97.             Console.ReadLine();
  99.         }
  100.     }
  101. }

希望这对你有所帮助。

英文:

I'm trying to use the Queryable.Join method to join Lists of objects together, but I want to use the WHERE clause against the inner table. I cannot reverse the order of the join tables in this case. When I add a WHERE clause it only "sees" the fields in the outer table. Here's a sample that reproduces my problem:

  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. namespace JoinsWithIQueriable
  5. {
  6. internal class Program
  7. {
  8. class Person
  9. {
  10. public string Name { get; set; }
  11. public int Id { get; set; }
  12. }
  13. class Pet
  14. {
  15. public string Name { get; set; }
  16. public int WeightInLbs { get; set; }
  17. public Person Owner { get; set; }
  18. public Species PetSpecies { get; set; }
  19. }
  20. class Species
  21. {
  22. public string Name { get; set; }
  23. }
  24. static void Main(string[] args)
  25. {
  26. Species dog = new Species { Name = &quot;Dog&quot; };
  27. Species cat = new Species { Name = &quot;Cat&quot; };
  28. Person magnus = new Person { Name = &quot;Hedlund, Magnus&quot;, Id = 1};
  29. Person terry = new Person { Name = &quot;Adams, Terry&quot;, Id = 2 };
  30. Person charlotte = new Person { Name = &quot;Weiss, Charlotte&quot;, Id = 3 };
  31. Person alergictopets = new Person { Name = &quot;Scratchy, Itchy&quot;, Id = 4 };
  32. Pet barley = new Pet { Name = &quot;Barley&quot;, WeightInLbs = 10, Owner = terry, PetSpecies = cat };
  33. Pet boots = new Pet { Name = &quot;Boots&quot;, WeightInLbs = 11, Owner = terry, PetSpecies = cat };
  34. Pet whiskers = new Pet { Name = &quot;Whiskers&quot;, WeightInLbs = 12, Owner = charlotte, PetSpecies = cat };
  35. Pet daisy = new Pet { Name = &quot;Daisy&quot;, WeightInLbs = 40, Owner = magnus, PetSpecies = dog};
  36. List&lt;Person&gt; people = new List&lt;Person&gt; { magnus, terry, charlotte, alergictopets };
  37. List&lt;Pet&gt; pets = new List&lt;Pet&gt; { barley, boots, whiskers, daisy };
  38. List&lt;Species&gt; species = new List&lt;Species&gt; { dog, cat};
  39. // Join the list of Person objects and the list of Pet objects
  40. // to create a list of person-pet pairs where each element is
  41. // an anonymous type that contains the name of pet and the name
  42. // of the person that owns the pet.
  43. Console.WriteLine(&quot;query&quot;);
  44. var query = people.AsQueryable().Join(pets,
  45. person =&gt; person,
  46. pet =&gt; pet.Owner,
  47. (person, pet) =&gt;
  48. new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs });
  49. foreach (var obj in query)
  50. {
  51. Console.WriteLine(
  52. &quot;Owner {0} - Pet {1} - Pet weight {2} &quot;,
  53. obj.OwnerName,
  54. obj.Pet, 
  55. obj.Weight.ToString());
  56. }
  57. /*
  58. This code produces the following output:
  59. Owner Hedlund, Magnus - Pet Daisy - Pet weight 40
  60. Owner Adams, Terry - Pet Barley - Pet weight 10
  61. Owner Adams, Terry - Pet Boots - Pet weight 11
  62. Owner Weiss, Charlotte - Pet Whiskers - Pet weight 12
  63. */
  64. Console.WriteLine(&quot;querywithwhere&quot;);
  65. //Want to filter for pets that weight less than 12 lbs
  66. var querywithwhere = people.AsQueryable()
  67. .Join(pets,
  68. person =&gt; person,
  69. pet =&gt; pet.Owner,
  70. (person, pet) =&gt;
  71. new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs });
  72. foreach (var obj in querywithwhere)
  73. {
  74. Console.WriteLine(
  75. &quot;Owner {0} - Pet {1} - Pet weight {2} &quot;,
  76. obj.OwnerName,
  77. obj.Pet,
  78. obj.Weight.ToString());
  79. }
  80. Console.WriteLine(&quot;Press any key to continue...&quot;);
  81. Console.ReadLine();
  82. }
  83. }
  84. }

答案1

得分: 1

要么你可以在与 people 连接之前使用包含记录 WeightInLbs &lt; 12 的方式筛选 pets。

  1. var querywithwhere = people.AsQueryable()
  2.     .Join(pets.Where(x =&gt; x.WeightInLbs &lt; 12),
  3.         person =&gt; person,
  4.         pet =&gt; pet.Owner,
  5.         (person, pet) =&gt;
  6.             new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs });

或者你可以在连接后使用 .Where() 子句筛选记录。

  1. var querywithwhere = people.AsQueryable()
  2.     .Join(pets,
  3.         person =&gt; person,
  4.         pet =&gt; pet.Owner,
  5.         (person, pet) =&gt;
  6.             new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs })
  7.     .Where(x =&gt; x.Weight &lt; 12);
英文:

Either you can filter the pets with containing the record with WeightInLbs &lt; 12 before join with people

  1. var querywithwhere = people.AsQueryable()
  2. 	.Join(pets.Where(x =&gt; x.WeightInLbs &lt; 12),
  3. 		person =&gt; person,
  4. 		pet =&gt; pet.Owner,
  5. 		(person, pet) =&gt;
  6. 			new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs });

Or you can filter the record after joining with .Where() clause.

  1. var querywithwhere = people.AsQueryable()
  2. 	.Join(pets,
  3. 		person =&gt; person,
  4. 		pet =&gt; pet.Owner,
  5. 		(person, pet) =&gt;
  6. 			new { OwnerName = person.Name, Pet = pet.Name, Weight = pet.WeightInLbs })
  7. 	.Where(x =&gt; x.Weight &lt; 12);

huangapple
  • 本文由 发表于 2023年6月5日 19:54:56
  • 转载请务必保留本文链接：https://go.coder-hub.com/76406170.html
  • c#
  • join
  • linq
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定