score:3

Accepted answer
var count = 50;

var alreayUsed = from au in repository.GetEntity<AlreadyUsed>() 
                 select au.Id;

var notUsed = (from nu in repository.GetEntity<Sample>()
              where !alreadyUsed.Contains(nu.Id) && nu.References.Count() > 0
              orderby nu.Name
              select new CustomClass
              {
                 CName = nu.Name,
                 CId = nu.Id
              }).Take(count).ToArray();

Notice that I added "nu.References.Count() > 0"

I assume that you setup the association relationship called References correctly in your data model so that there are many "Reference" objects in for every Sample object.

score:0

You should be able to use the same technique from your alreadyUsed sample. EG:

var reference = from r in repository.GetEntity<Reference>()
                select r.Id;

var notUsed = (from nu in repository.GetEntity<Sample>()
               where !alreadyUsed.Contains(nu.Id)
               && reference.Contains(nu.Id)
               select new CustomClass
               {
                   CName = nu.Name,
                   CId = nu.Id
               }).Take(count).ToArray();

However, if you do have an association made between the Sample table and the Reference table, then you should probably use Paul's method.

score:0

This should achieve what you are looking for. Of course there are many ways to do it. Personally I'd write it this way.

var items = (from r in repository.GetEntity<Reference>()
             join s in repository.GetEntity<Sample>()
                 on r.FkId equals s.Id
             where !repository.GetEntity<AlreadyUsed>().Contains(s.Id)
             orderby s.Name
             select new CustomClass
             {
                 CName = s.Name,
                 CId = s.Id
             })
            .Take(count)
            .ToArray();

Related Articles