sql to linq transformation

Posted on 2012-08-16
Last Modified: 2012-08-20
I need to change the following query to linq.  Please help

SELECT        Product_ProductServiceName, ProductClass
FROM            Table1 AS a
WHERE        (Letter = 'y') AND (Product_ProductServiceName NOT IN
                             (SELECT        ProductServiceName
                               FROM            Table2
                               WHERE        (fk_Planid = '30')))
Question by:mwhodges
    LVL 74

    Expert Comment

    by:käµfm³d 👽

    var query = from a in db.Table1
                where a.Letter == "y" && !(from b in db.Table2
                                           where b.fk_Planid == "30"
                                           select o.CustomerID).Contains(a.Product_ProductServiceName)
                select new
                    ProductServiceName = Product_ProductServiceName,
                    ProductClass = ProductClass

    Open in new window


    Author Comment

    I'm using sorry.  I tried to do the conversion but I have a question where did the o .customerid come from?  Also when I get beyond the select new statment I get several other errors.  Thank you so much for your help.
    LVL 74

    Accepted Solution

    where did the o .customerid come from?
    My fault--I forgot to change that piece. Try this VB version:

    Dim query = From a In db.Table1 _
                Where a.Letter = "y" AndAlso Not (From b in db.Table2 _
                                                  Where b.fk_Planid = "30" _
                                                  Select b.ProductServiceName).Contains(a.Product_ProductServiceName) _
                Select New With _
                { _
                    a.ProductServiceName = Product_ProductServiceName, _
                    a.ProductClass = ProductClass _

    Open in new window


    Author Closing Comment

    Thank you so much!!  It worked perfectly

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now