Advertisement
Guest User

Untitled

a guest
Oct 27th, 2016
69
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.31 KB | None | 0 0
  1. SELECT
  2. `Distinct1`.`UserId`,
  3. `Distinct1`.`FirstName`,
  4. `Distinct1`.`LastName`,
  5. `Distinct1`.`EmailAddress`,
  6.  
  7. FROM ( SELECT DISTINCT
  8. `Extent2`.`UserId`,
  9. `Extent2`.`FirstName`,
  10. `Extent2`.`LastName`,
  11. `Extent2`.`EmailAddress`,
  12.  
  13. FROM `AssistantTo` AS `Extent1`
  14. INNER JOIN `User` AS `Extent2` ON `Extent1`.`AssistantId` = `Extent2`.`UserId`
  15. INNER JOIN `CustomTagUser` as `Extent3` ON `Extent3`.`UserId` = `Extent2`.`UserId`
  16. WHERE `Extent1`.`OwnerId` = 274 AND `Extent3`.`CustomTagId` = 114
  17. ) AS `Distinct1`
  18.  
  19. var assistants =
  20. dbContext.AssistantsTo
  21. .Include(x => x.Assistant)
  22. .Include(x => x.Assistant.CustomTagUser)
  23. .Where(at =>
  24. at.OwnerId == currentUser.UserId
  25. && (at.Assistant.CustomTagUser.Count(y => y.CustomTagId == filter) > 0)
  26. )
  27. .Select(at => at.Assistant)
  28. .Distinct()
  29. .ToList();
  30.  
  31. `Extent3`.`CustomTagId` = 114
  32.  
  33. public class AssistantTo
  34. {
  35. [Key]
  36. [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  37. public int AssistantToId { get; set; }
  38. public int AssistantId { get; set; }
  39. [ForeignKey("AssistantId")]
  40. public virtual User Assistant { get; set; }
  41. public int OwnerId { get; set; }
  42. [ForeignKey("OwnerId")]
  43. public virtual User Owner { get; set; }
  44. }
  45.  
  46. public class CustomTagUser
  47. {
  48. [Key]
  49. public int CustomTagUserId { get; set; }
  50. public int CustomTagId { get; set; }
  51. public int UserId { get; set; }
  52. public DateTime CreatedOn { get; set; }
  53. [ForeignKey("CustomTagId")]
  54. public virtual CustomTags CustomTags { get; set; }
  55. [ForeignKey("UserId")]
  56. public virtual User User { get; set; }
  57. }
  58.  
  59.  
  60. [Table("User")]
  61. public class User
  62. {
  63. [Key]
  64. [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  65. public int UserId { get; set; }
  66.  
  67. public string FirstName { get; set; }
  68. public string LastName { get; set; }
  69. public string EmailAddress { get; set; }
  70. public string Password { get; set; }
  71. .....
  72. public ICollection<CustomTagUser> CustomTagUser { get; set; }
  73. }
  74. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement