code_junkie

How to load Many to many LINQ query?

Nov 10th, 2011
122
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.96 KB | None | 0 0
  1. Post <-> PostTag <-> Tag
  2.  
  3. PostID Title
  4. 1, 'Foo'
  5. 2, 'Bar'
  6. 3, 'Baz'
  7.  
  8. TagID Name
  9. 1, 'Foo'
  10. 2, 'Bar'
  11.  
  12. PostID TagID
  13. 1 1
  14. 1 2
  15. 2 2
  16.  
  17. public IList<Post> GetPosts(int page, int record)
  18. {
  19. var options = new DataLoadOptions();
  20. options.LoadWith<Post>(p => p.PostTags);
  21. options.LoadWith<PostTag>(pt => pt.Tag);
  22. using (var db = new DatabaseDataContext(m_connectionString))
  23. {
  24. var publishDateGmt = (from p in db.Posts
  25. where p.Status != PostStatus.Hidden
  26. orderby p.PublishDateGmt descending
  27. select p.PublishDateGmt)
  28. .Skip(page * record)
  29. .Take(record)
  30. .ToList()
  31. .Last();
  32. db.LoadOptions = options;
  33. return (from p in db.Posts
  34. where p.Status != PostStatus.Closed
  35. && p.PublishDateGmt >= publishDateGmt
  36. orderby p.PublishDateGmt descending
  37. select p)
  38. .Skip(page * record)
  39. .ToList();
  40. }
  41. }
  42.  
  43. DataLoadOptions o = new DataLoadOptions ( );
  44. o.LoadWith<Listing> ( l => l.ListingStaffs );
  45. o.LoadWith<ListingStaff> ( ls => ls.MerchantStaff );
  46. ctx.LoadOptions = o;
  47.  
  48. IQueryable<Listing> listings = (from a in ctx.Listings
  49. where a.IsActive == false
  50. select a);
  51. List<Listing> list = listings.ToList ( );
  52.  
  53. SELECT [t0].*, [t1].*, [t2].*, (
  54. SELECT COUNT(*)
  55. FROM [dbo].[LStaff] AS [t3]
  56. INNER JOIN [dbo].[MStaff] AS [t4] ON [t4].[MStaffId] = [t3].[MStaffId]
  57. WHERE [t3].[ListingId] = [t0].[ListingId]
  58. ) AS [value]
  59. FROM [dbo].[Listing] AS [t0]
  60. LEFT OUTER JOIN ([dbo].[LStaff] AS [t1]
  61. INNER JOIN [dbo].[MStaff] AS [t2] ON [t2].[MStaffId] = [t1].[MStaffId]) ON
  62. [t1].[LId] = [t0].[LId] WHERE NOT ([t0].[IsActive] = 1)
  63. ORDER BY [t0].[LId], [t1].[LStaffId], [t2].[MStaffId]
  64.  
  65. public IList<Post> GetPosts(int page, int records)
  66. {
  67. var options = new DataLoadOptions();
  68. options.LoadWith<Post>(p => p.PostTags);
  69. options.LoadWith<PostTag>(pt => pt.Tag);
  70. using (var db = new BlogDataContext())
  71. {
  72. db.LoadOptions = options;
  73. return (from p in db.Posts
  74. where p.Status != PostStatus.Closed
  75. orderby p.PublishDateGmt descending
  76. select p)
  77. .Skip(page * records)
  78. //.Take(records)
  79. .ToList();
  80. }
  81. }
  82.  
  83. DataLoadOptions options = new DataLoadOptions();
  84. options.LoadWith<Post>(p => p.PostTag);
  85. options.LoadWith<PostTag>(pt => pt.Tag);
  86.  
  87. var options = new DataLoadOptions();
  88. options.LoadWith<Post>(p => p.PostTags);
  89. options.LoadWith<PostTag>(pt => pt.Tag);
  90. using (var db = new BlogDataContext())
  91. {
  92. db.LoadOptions = options;
  93. return (from p in db.Posts
  94. where p.Status != PostStatus.Closed
  95. orderby p.PublishDateGmt descending
  96. select p);
  97. }
  98.  
  99. var q = from p in db.Posts
  100. where db.Posts.Take(10).Contains(p)
  101. select p;
  102.  
  103. SELECT p.PostID, p.Title, pt.PostID, pt.TagID, t.TagID, t.Name FROM Posts p
  104. JOIN PostsTags pt ON p.PostID = pt.PostID
  105. JOIN Tags t ON pt.TagID = t.TagID
  106. WHERE p.PostID IN (SELECT TOP 10 PostID FROM Posts)
Add Comment
Please, Sign In to add comment