CODEWITHSUNDEEP
c#sqllinq
John
John

Reputation: 35

convert sql subquery to linq

I'm trying to convert a simple subquery to LINQ (c#):

select * from tblFundtoFiscalYear ftfy
where ftfy.FiscalYear = 2015 
    and ftfy.FundID =(
        SELECT FundID
        FROM tblAccountDirectParent]
        where ParentAccountID = 19 )

Something along the lines of:

var queryFTFY =
    from ftfy in FRATContext.tblFundtoFiscalYear
    where ftfy.FiscalYear == 2015 
        && ftfy.FundID =
            (from adp in FRATContext.tblAccountDirectParent
             where adp.ParentAccountID == 19
             select adp.FundID)
    select new
    {
        ftfy.FundtoFiscalYearID
    };

Assistance would be appreciated.

Upvotes: 0

Views: 325

Answers (1)

Douglas
Douglas

Reputation: 54877

Your SQL relies on the assumption that the subquery returns just one result. You can make this explicit in your LINQ:

var queryFTFY =
    from ftfy in FRATContext.tblFundtoFiscalYear
    where ftfy.FiscalYear == 2015 && ftfy.FundID ==
    (
        from adp in FRATContext.tblAccountDirectParent
        where adp.ParentAccountID == 19
        select adp.FundID
    ).Single()
    select ftfy;

Alternatively, you could reformulate the query (in both cases) as a join:

var queryFTFY =
    from ftfy in FRATContext.tblFundtoFiscalYear
    join adp in FRATContext.tblAccountDirectParent
    on ftfy.FundID equals adp.FundID
    where ftfy.FiscalYear == 2015 
       && adp.ParentAccountID == 19
    select ftfy;

Upvotes: 3

Related Questions