Help with linq to sql query

Based on the following tables

Sample
Id     Name
---------------
S1     SomeName
S2     OtherName
S3     AnotherName

AlreadyUsed
Id
---------
S2

Reference
Id    FkId
---------
T1    S1
T2    S1

I want to achieve the following "select only those entries from Sample table which have no entries in AlreadyUsed and have atleast one entry in Reference table."

I was able to write query for the first part but confused with the seco开发者_C百科nd half. Below is what i could come up with to get "select only those entries from sample table which have no entries in AlreadyUsed table"

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)
              orderby nu.Name
              select new CustomClass
              {
                 CName = nu.Name,
                 CId = nu.Id
              }).Take(count).ToArray();

Also pls. suggest if there is a better way to write the above query.

thanks.

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.

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();

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.