CODEWITHSUNDEEP
linq-to-sql
Brian Roisentul
Brian Roisentul

Reputation: 4730

How to write this SQL query in Linq To SQL

I would like to know how to write the following SQL query in Linq. I've tried but without luck.

/*Variable*/
topicId = '4a31c2ee-48af-4b87-b300-112acb822ad0'

select * from Contents c
left join ContentToTopicRelations ctr on ctr.ContentId = c.ContentId and ctr.TopicId = topidId
where ctr.ContentId is null

Basically, I would like to get all the contents that are not in ContentToTopicRelations table for a particular topicId.

Upvotes: 0

Views: 419

Answers (3)

oryol
oryol

Reputation: 5248


dataContext.Contents.Where(c =>
  !dataContext.ContentToTopicRelations.Any(ctr =>
     ctr.ContantId == c.ContentId &&
     ctr.TopicId == topicId))

It is identical to select * from Content where not exists(...). And in general case it is better than left join and checking for null (it depends on the table statistics but..), because it will give (again, in general case) semi left join instead of left join (in the execution plan).

For left join itself use code like following (but I recommend using code which generates not exists for your task):


from c in dataContext.Contents
join tempCTR in dataContext.ContentToTopicRelations
  on new { c.ContentId, topicId) equals new { tempCTR.ContentId, tempCTR.TopicId }
  into tempCTRCollection
  from ctr in tempCTRCollection.DefaultIfEmpty()
where ctr == null
select c

Upvotes: 2

Amy B
Amy B

Reputation: 110071

topicId = '4a31c2ee-48af-4b87-b300-112acb822ad0' 

IQueryable<Content> query =
  from c in dataContext.Contents
  where !c.ContentToTopicRelations.Any(ctr => ctr.TopicId == topicId)
  select c;

Upvotes: 0

codewrath
codewrath

Reputation: 51

topicvariable = "sdfsdfsdfsdf sdfsdfsdfsdfsdfsdfsdfsd";

var results = from c in Contents where c.TopicId = topicvariable select c;

Upvotes: -1

Related Questions