none
LINQ to Entities Query 0-1 to Many RRS feed

  • Question

  • Hi All

    Absolute Learner on Linq to Entities.

    See below for a snippet from edmx.

     

    Iam trying to come up with a L2E query that gives me a list of Club.Id, Club.Name for all clubs that DO NOT have any ClubACL records containing a given ClubACL.UserId (for this example assume UserId of 2).

    I have tried something like this (VB).

    From c In Clubs
    	Select New With { 
    	c.id, 
    	c.Name, 
    	.ACLs = from a in c.ClubACLs
    	where a.UserId=2 
    	}
    


    Which gives me ALL clubs and a collection of ClubACLs (either empty or containing ClubACLs with UserId=2).  How do I turn this into a query which just gives me Club.Id and Club.Name for those clubs that dont have any ClubACLs with userid = 2?

     

    Hoping someone can help.

     

     


    ____________________________________________________ Regards The.Blacksmith
    Sunday, September 4, 2011 11:52 AM

Answers

  • Hi The.Blacksmith;

    Because it is possible to have a Club without any ClubACL records you will need to test for that as well as NOT having any with cid.UserId <> 2. I would use a query like the following.

    Dim query = From c In ctx.Clubs
                Where c.ClubACLs.Count() = 0 OrElse c.ClubACLs.All(Function(cid) cid.UserId <> 2)
                Select New With {
                    c.Id,
                    c.Name
                }
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by The.Blacksmith Monday, September 5, 2011 12:28 AM
    Sunday, September 4, 2011 2:58 PM

All replies

  • As often happens when you post a query on a forum, you seem to be able to solve it a short time later...

    I think this will work:

     

    From c in Clubs
    Where c.ClubACLs.All(Function(a) a.UserId <> 2)
    Select New With {c.Id, c.Name}
    

     


    That seems to work OK.  Verification / helpful comments / suggested alternatives would still be greatly appreciated.

     


    ____________________________________________________ Regards The.Blacksmith
    Sunday, September 4, 2011 12:15 PM
  • Hi The.Blacksmith;

    Because it is possible to have a Club without any ClubACL records you will need to test for that as well as NOT having any with cid.UserId <> 2. I would use a query like the following.

    Dim query = From c In ctx.Clubs
                Where c.ClubACLs.Count() = 0 OrElse c.ClubACLs.All(Function(cid) cid.UserId <> 2)
                Select New With {
                    c.Id,
                    c.Name
                }
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by The.Blacksmith Monday, September 5, 2011 12:28 AM
    Sunday, September 4, 2011 2:58 PM
  • Fernando to the rescue again.

    Thanks for that.  Just looking at it I can see your point.  It is certainly more precise so I'll use that.

     

     


    ____________________________________________________ Regards The.Blacksmith
    Monday, September 5, 2011 12:28 AM
  •  

    Not a problem The.Blacksmith, always glad to help.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, September 5, 2011 4:30 AM