CODEWITHSUNDEEP
c#linqlinq-to-sqlc#-4.0
stackoverflowuser
stackoverflowuser

Reputation: 22380

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 second 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.

Upvotes: 2

Views: 153

Answers (3)

Chris Wenham
Chris Wenham

Reputation: 24017

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.

Upvotes: 0

Jeff Mercado
Jeff Mercado

Reputation: 134811

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

Upvotes: 0

Paul Mendoza
Paul Mendoza

Reputation: 5787

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.

Upvotes: 3

Related Questions