none
Using "IN" key word in sql. Don't know how to do this in LINQ RRS feed

  • Question

  • Hello,

    This is my sql query.

    select doc_id, title, document_category_code, doc_file
     from flx_document
    where doc_id in (select doc_id from flx_help_doc where pl_id = &planId)

    I need to do the same thing in LINQ.

    I tried this

     

    var helpDocs = (

     

    from hlpdoc in ctx.FlxHelpDocs

     

    where hlpdoc.FlxPlan.PlId == planId

     

    select new

    {

    hlpdoc.FlxDocument.DocId

    }).ToList();

    queryResult = (

     

    from doc in ctx.FlxDocuments

     

    where doc.DocumentCategoryCode == docCagetoryCode

    && helpDocs.Contains(doc.DocId)

     

    orderby doc.Title, doc.DisplayOrder

     

    select new

    {

    doc.DocId,

    PlanTitle =

    "plan title",

    doc.Title,

    DocumentType = doc.DocumentCategoryCode,

    doc.DocFile

    }).ToList();

    But I am getting a compilation error "Cannot convert lambda expression to type 'string' because it is not a delegate type"

    Could anybody tell me how to write my qery in LINQ?

    Thank you very much.

    Monday, December 19, 2011 4:58 PM

All replies

  • Hi Sandra6;

    This single query will replace the two queries you have and should give you what you are looking for.

    queryResult = 
        (from doc in ctx.FlxDocuments 
         where doc.DocumentCategoryCode == docCagetoryCode && 
           ctx.FlxHelpDocs.Where( p => p.PlId == planId)
                          .Select( dId => dId.FlxDocument.DocId).Contains(doc.DocId)
         orderby doc.Title, doc.DisplayOrder 
         select new 
         {
             doc.DocId,
             PlanTitle =  "plan title", 
             doc.Title,
             DocumentType = doc.DocumentCategoryCode,
             doc.DocFile
         }).ToList();
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, December 19, 2011 7:07 PM
  • var query=from fx in flx_document
              let temp=flx_help_doc.Where(h=>h.pl_id==planId).Select(h=>h.doc_id)
              where temp.Contains(fx.doc_id)
              select new {...}
    




    Best Regards,
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, December 21, 2011 6:29 AM
  • Hi Fernando and Tim,

    I am sorry for the late response. I really appreciate your help.

    I tried both solutions and in both cases I had to make a small change to fix a compilation error. I guess I wasn't clear that there is an n:n relationship between FlxDocument and FlxPlan and that FlxHelpDoc is the entity that links the two. I am sorry, the names are not very clear.

    So I tried those two queries

    queryResult =

    (

    from doc in ctx.FlxDocuments

     

    where doc.DocumentCategoryCode == docCagetoryCode &&

    ctx.FlxHelpDocs.Where(p => p.FlxPlan.PlId == planId)

    .Select(dId => dId.FlxDocument.DocId).Contains(doc.DocId)

     

    orderby doc.Title, doc.DisplayOrder

     

    select new

    {

    doc.DocId,

    PlanTitle =

    "plan title",

    doc.Title,

    DocumentType = doc.DocumentCategoryCode,

    doc.DocFile

    }).ToList();

    

    and

    

    

    queryResult =

    (

    from doc in ctx.FlxDocuments

     

    let temp = ctx.FlxHelpDocs.Where(h => h.FlxPlan.PlId == planId).Select(h => h.FlxDocument.DocId)

     

    where temp.Contains(doc.DocId)

     

    select new

    {

    doc.DocId,

    PlanTitle =

    "plan title"

    ,

    doc.Title,

    DocumentType = doc.DocumentCategoryCode,

    doc.DocFile

    }).ToList();

    

    In both cases I got the same run-time error

    "LINQ to Entities does not recognize the method 'Boolean Contains[Int64](System.Linq.IQueryable`1[System.Int64], Int64)' method, and this method cannot be translated into a store expression."}

    Any ideas?

    Thank you so much.

    Wednesday, December 21, 2011 9:38 PM
  • Hi Sandra6,

    Welcome to MSDN Forum.

    Could you please post your model schema here? This is, so we can help you more effectly.

    In fact, in Entity Framework, you can just create a FlxDoument entity and a FlxPlan entity and create a many to many relationship between them. The relationship entity seems doesn't make sense.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Friday, December 23, 2011 3:19 AM
    Moderator
  • Hi Sandra6,

    Have you solved the issue? I look forward to hearing from you.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us
    Tuesday, December 27, 2011 1:51 AM
    Moderator
  • When working with n:n relationships, then you will avoid a lot of hassle by beginning with the linked table, and then in the end selecting the needed table.

    var query = from row in context.DocumentCategory
        where row.Document.DocumentId == xx
        select row.Document;

     

    Monday, January 2, 2012 12:49 PM