CODEWITHSUNDEEP
c#sqllinq
T. R. Dhage
T. R. Dhage

Reputation: 1

Write sql query to linq

I am having following query in sql :

SELECT [definition],[pos]
  FROM [WordNet].[dbo].[synsets]
  where synsetid in(SELECT [synsetid] FROM [WordNet].[dbo].[senses] 
  where wordid = (select [wordid]FROM [WordNet].[dbo].[words]
  where lemma = 'searchString'))

I had tried this for sql to linq :

long x = 0;

if (!String.IsNullOrEmpty(searchString))
{
    var word = from w in db.words
               where w.lemma == searchString
               select w.wordId;
    x = word.First();

   var sence = from s in db.senses
               where (s.senseId == x)
               select s;
   var synset = from syn in db.synsets
                where sence.Contains(syn.synsetId)
                select syn;

But I am getting following error at sence.Contains()

  Error1:Instance argument: cannot convert from
  'System.Linq.IQueryable<WordNetFinal.Models.sense>' to
  'System.Linq.ParallelQuery<int>'

Upvotes: 0

Views: 847

Answers (3)

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

You're comparing an entire sense row with a synsetId, which is not correct. You're also splitting the original query into two separate queries by using First() which triggers an evaluation of the expression so far. If you can live with not returning an SQL error if there are duplicates in words, you can write the query as something like this;

if (!String.IsNullOrEmpty(searchString))
{
    var wordIds = from word in db.words
                  where word.lemma == searchString
                  select word.wordId;

    var synsetIds = from sense in db.senses
                    where wordIds.Contains(sense.wordId)
                    select sense.synsetId;

    var result = (from synset in db.synsets
                  where synsetIds.Contains(synset.synsetId)
                  select new {synset.definition, synset.pos}).ToList(); 
}

The ToList() triggering the evaluation once for the entire query.

You could also just do it using a simpler join;

var result = (from synset in db.synsets
              join sense in db.senses on synset.synsetId equals sense.synsetId
              join word in db.words on sense.wordId equals word.wordId
              select new {synset.definition, synset.pos}).ToList();

Upvotes: 0

Piotr Stapp
Piotr Stapp

Reputation: 19820

Below code:

var sence = from s in db.senses
               where (s.senseId == x)
               select s;

Returns object of type: WordNetFinal.Models.sense, but in where sence.Contains(syn.synsetId) you are trying to search in it syn.synsetId which is an integer.

So you should change above code to:

var sence = from s in db.senses
                   where (s.senseId == x)
                   select s.senseId;

Upvotes: 2

AD.Net
AD.Net

Reputation: 13399

x seems to be of Word type, which is not the type of Id (probably int or long).

Upvotes: 0

Related Questions