LINQ Query to determine whether or not a relationship exists-CodePudding

In this program, I have 3 models: Client, Brokerage and Subscription. A Subscription connects Clients to Brokerages. A Client has a subscription to one or many brokerages.

public class Client
{
  public int Id { get; set; }

  [Required, StringLength(50), DisplayName("Last Name")]
  public string LastName { get; set; }

  [Required, StringLength(50), DisplayName("First Name")]
  public string FirstName { get; set; }

  [DataType(DataType.Date),DisplayFormat(DataFormatString = "{0:yyyy-MM-dd}",ApplyFormatInEditMode = true),Display(Name = "Birth Date")]
  public DateTime BirthDate { get; set; }

  [DisplayName("Full Name")] 
  public string FullName
  {
    get { return LastName   ", "   FirstName; }
  }

  public ICollection<Subscription> Subscriptions { get; set; }
}

public class Brokerage
{
  [DatabaseGenerated(DatabaseGeneratedOption.None), DisplayName("Registration Number"), Required]
  public string Id { get; set; }

  [Required]
  public string Title { get; set; }

  [DataType(DataType.Currency), Column(TypeName="money")]
  public decimal Fee { get; set; }

  public ICollection<Subscription> Subscriptions { get; set; }
}

public class Subscription
{
  public int ClientId { get; set; }
  public string BrokerageId { get; set; }
  public Client Client { get; set; }
  public Brokerage Brokerage { get; set; }
}

Right now, I am trying to write a LINQ query so that a user gets a list of existing brokerages, and the option to either subscribe or unsubscribe from that brokerage depending on whether or not the user is subscribed to the brokerage.

To do this, I have a ViewModel that contains the BrokerageId, the Title of the brokerage, and a boolean to represent if the client is a member or not. This ViewModel gets created in the Controller with this LINQ query:

var subscriptions = (
  from b in _context.Brokerages
  join s in _context.Subscriptions on b.Id equals s.BrokerageId
  select new BrokerageSubscriptionsViewModel
  {
    BrokerageId = b.Id,
    Title = b.Title,
    IsMember = s.ClientId == id
  }).ToList();

This is almost working.

The expected result should be as follows for a user that is subscribed to all brokerages:

A1 : {Unsubscribe}
A2 : {Unsubscribe}
A3 : {Unsubscribe}

But what I'm getting is the results from the entire Subscription table,

A1 : {Unsubscribe}
A1 : {Subscribe}
A1 : {Subscribe}
A2 : {Unsubscribe}
A2 : {Subscribe}
A2 : {Subscribe}

This is because it is pulling all the rows from the Subscription table, and if the client ID doesn't match, it displays "duplicates". I have tried adding a where s.ClientId == id clause, but this makes it so that users who have no active subscriptions see nothing, when they should see the option to subscribe to any existing brokerages.

I've been struggling with this for a long time and would appreciate any help avaialable.

CodePudding user response：

The equivalent of EXISTS in Linq is .Any().

If you have your relationships correctly defined then you can use navigation properties.

var subscriptions = (
  from b in _context.Brokerages
  select new BrokerageSubscriptionsViewModel
  {
    BrokerageId = b.Id,
    Title = b.Title,
    IsMember = b.Subscriptions.Any(),
  }).ToList();

Otherwise, use a "subquery"

var subscriptions = (
  from b in _context.Brokerages
  select new BrokerageSubscriptionsViewModel
  {
    BrokerageId = b.Id,
    Title = b.Title,
    IsMember = _context.Subscriptions.Any(s => b.Id == s.BrokerageId),
  }).ToList();