none
How to convert sub query in linq using entity framwork RRS feed

  • Question

  • Hi,

    What is the best way to convert  following query into linq- 

    select * 
    , (select 
     count([ContractStatus] )
     from contracts where [ContractStatus]
    in ('ACTIVE', 'PM CONTRACT', 'T&M ONLY')
    and contracts.jobid=jobs.jobid
     )
    from jobs
    
    Thanks

    • Moved by CoolDadTx Thursday, August 8, 2019 2:05 PM EF related
    Thursday, August 8, 2019 12:27 PM

All replies

  • Hello,

    For the IN condition use an array of values to check via .Contains.

    In the following example I want three specific countries from the year 2014.

    public void CountOrders()
    {
        string[] shipCountries = { "Switzerland", "Germany", "USA" };
        int year = 2014;
        using (var context = new NorthWindContext())
        {
            context.Configuration.LazyLoadingEnabled = false;
            var results = context
                .Orders.AsNoTracking()
                .Where(ord => shipCountries.Contains(ord.ShipCountry) && 
                              DbFunctions.TruncateTime(ord.OrderDate).Value.Year == year)
                .ToList();
    
            var count = results.Count;
        }
    }


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, August 8, 2019 1:41 PM
  • Hi AnkitKumar2016,

    Here is the test ".edmx".

    Now, you can refer to the following code to achieve it via LINQ.

        Test2Entities test2Entities = new Test2Entities();
    
        var statuscounts = from j in test2Entities.jobs
                            join c in test2Entities.contracts
                            on j.jobid equals c.jobid
                            where c.ContractStatus == "ACTIVE" ||
                            c.ContractStatus == "PM CONTRACT" ||
                            c.ContractStatus == "T&M ONLY"
                            select new { sid = c.jobid, sstatus = c.ContractStatus };
    
        var jobcounts = from j in test2Entities.jobs
                        select new { jid = j.jobid, jtype = j.jobtype, count = statuscounts.Where(s => s.sid == j.jobid).Count() };
    
        foreach (var i in jobcounts)
        {
            Console.WriteLine(i.jid + "," + i.jtype + "," + i.count);
        }

    Hope this can help you.

    Regards,

    Kyle


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 9, 2019 4:55 AM
    Moderator