Help with linq to sql query

Question

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.

Is the table name "Sample" or "NotUsed"?

Jeff Mercado
– Jeff Mercado

2011-02-21 23:43:55 +00:00
Commented Feb 21, 2011 at 23:43 — Jeff Mercado
– Jeff Mercado, Commented Feb 21, 2011 at 23:43
You used "NotUsed" entity but is NotUsed really "Sample"?

Paul Mendoza
– Paul Mendoza

2011-02-21 23:46:18 +00:00
Commented Feb 21, 2011 at 23:46 — Paul Mendoza
– Paul Mendoza, Commented Feb 21, 2011 at 23:46

Paul Mendoza · Accepted Answer · 2011-02-21 23:46:32Z

3

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.

answered Feb 21, 2011 at 23:46

Paul Mendoza

5,82812 gold badges57 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jeff Mercado · Accepted Answer · 2011-02-21 23:49:26Z

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

answered Feb 21, 2011 at 23:49

Jeff Mercado

136k34 gold badges268 silver badges282 bronze badges

Comments

Chris Wenham · Accepted Answer · 2011-02-21 23:49:47Z

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.

edited Feb 21, 2011 at 23:49

answered Feb 21, 2011 at 23:44

Chris Wenham

24.1k13 gold badges62 silver badges73 bronze badges

1 Comment

juharr Over a year ago

You should be selecting r.FkId for your reference query.

Collectives™ on Stack Overflow

Help with linq to sql query

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related