CODEWITHSUNDEEP
c#linq
vaindil
vaindil

Reputation: 7864

Check if single element is in a list in LINQ when the single is provided first

I'm using LINQ to SQL for a service in a project. I have three entities involved: PCode is a property of PurchaseOrder, as well as in an M:N with User.

PCode (M:N) User
PCode (1:M) PurchaseOrder

The service is for PurchaseOrder, so that table is being queried. Because of that, using .Where() requires p => p.PCode at the beginning to utilize the value. I know of .Contains() and the other similar commands, but they all require that the list come first and I can't do that with this type of query. I ultimately need to check whether the PCodeId in the specific PurchaseOrder is in the list of PCodeIds associated with the current user. How can I filter this way in LINQ to SQL? I feel like this is really simple, I just can't figure it out.

Simplified User class:

public partial class User
{
    public virtual int Id { get; set; }
    public virtual ICollection<PCode> PCodes { get; set; }
}

PCode:

public partial class PCode
{
    public virtual int Id { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

PurchaseOrder:

public partial class PurchaseOrder
{
    public virtual int Id { get; set; }
    public virtual int PCodeId { get; set; }
}

Beginning of query:

// list to be searched can either be passed into the function
// or retrieved via navigation property when the query runs, either works

var query = _poRepository.Table;
if (condition)
    // query = query.Where( ? );
    // query = query.Where(p => p.PCode ... ? );

Upvotes: 0

Views: 503

Answers (1)

Robert McKee
Robert McKee

Reputation: 21477

var CurrentUser=db.Users
  .Include(u=>u.PCodes)
  .Where(u=>u.id==something);
if (CurrentUser.PCodes.Any(pc=>...))

You are also missing navigation properties on Purchase Order, and PCode:

public partial class PurchaseOrder
{
    public int Id { get; set; }  // Should not be virtual
    public int PCodeId { get; set; } // Should not be virtual
    public virtual PCode PCode { get; set; } // new Navigation
}
public partial class PCode
{
    public int Id { get; set; }  // Should not be virtual
    public virtual ICollection<User> Users { get; set; }
    public virtual ICollection<PurchaseOrders> PurchaseOrders { get; set; } // new Navigation
}

Or the reverse:

var po=db.PurchaseOrders
  .Include(po=>po.PCode)
  .Include(po=>po.PCode.Users)
  .First(po=>po.id==someid);

if (po.PCode.Users.Contains(currentUser)) ... 
or
if (po.PCode.Users.Any(u=>u.id==someuserid)) ...

or if all you have is a userid and poid:

if (db.PurchaseOrders.Any(po=>po.id==poid && po.PCode.Users.Any(u=>u.id==userid))) ...

Upvotes: 1

Related Questions