none
Sql query to Entity Framework RRS feed

  • Question

  • Hi. I have a Problem. I can't convert this EF or Linq

    My Query

    select f.FirmaID, sum(ToplamTutar) as [Toplam Borç],o.odenen as [Ödenen] ,(sum(ToplamTutar)-o.odenen) as Kalan from tblFirmaCariHesap as f
    left OUTER join (select FirmaID ,sum(OdemeTutari) as odenen from tblFirmaCariHesapOdeme as o
    group by o.FirmaID) as o on f.FirmaID =o.FirmaID
    group by o.odenen,f.FirmaID

    • Moved by Bob Beauchemin Monday, December 2, 2013 11:58 PM Moved here for best response
    Monday, December 2, 2013 5:02 PM

Answers

  • Hello,

    Since I do not know the exact table structure, I assume it is like below:

    CREATE TABLE [dbo].[tblFirmaCariHesap] (
    
        [FirmaID]     INT NOT NULL,
    
        [ToplamTutar] INT NULL,
    
        PRIMARY KEY CLUSTERED ([FirmaID] ASC)
    
    );
    
    CREATE TABLE [dbo].[tblFirmaCariHesapOdeme] (
    
        [tblFirmaCariHesapOdemeId] INT NOT NULL,
    
        [OdemeTutari]              INT NOT NULL,
    
        [FirmaID]                  INT NOT NULL,
    
        PRIMARY KEY CLUSTERED ([tblFirmaCariHesapOdemeId] ASC)
    
    );
    

    And then the LINQ query would be like:

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    var result = (from Odeme in db.tblFirmaCariHesapOdemes
    
                                  group Odeme by new { Odeme.FirmaID } into oo
    
                                  select new { FirmaID = oo.Key.FirmaID, odenen = oo.Sum(odeme => odeme.OdemeTutari) } into o
    
                                  from Hesap in db.tblFirmaCariHesaps
    
                                  where Hesap.FirmaID == o.FirmaID
    
                                  select new { Hesap.FirmaID, Hesap.ToplamTutar, o.odenen } into HO
    
                                  group HO by new { HO.odenen, HO.FirmaID } into f
    
                                  select new { FirmaID = f.Key.FirmaID, ToplamBorç = f.Sum(s => s.ToplamTutar), Ödenen = f.Key.odenen, Kalan = f.Sum(s => s.ToplamTutar) - f.Key.odenen }
    
                                  ).ToList();
    
                }
    

    The result:

    For sql statement in Linq, you can have a check link below:

    http://msdn.microsoft.com/en-us/vstudio/bb688088.aspx

    It shows a lot detail examples.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 3, 2013 6:56 AM
    Moderator

All replies

  • Moved to Entity Framework group on MSDN Forums for best response.
    Monday, December 2, 2013 11:57 PM
  • Hello,

    Since I do not know the exact table structure, I assume it is like below:

    CREATE TABLE [dbo].[tblFirmaCariHesap] (
    
        [FirmaID]     INT NOT NULL,
    
        [ToplamTutar] INT NULL,
    
        PRIMARY KEY CLUSTERED ([FirmaID] ASC)
    
    );
    
    CREATE TABLE [dbo].[tblFirmaCariHesapOdeme] (
    
        [tblFirmaCariHesapOdemeId] INT NOT NULL,
    
        [OdemeTutari]              INT NOT NULL,
    
        [FirmaID]                  INT NOT NULL,
    
        PRIMARY KEY CLUSTERED ([tblFirmaCariHesapOdemeId] ASC)
    
    );
    

    And then the LINQ query would be like:

    using (DataBaseFirstDBEntities db = new DataBaseFirstDBEntities())
    
                {
    
                    var result = (from Odeme in db.tblFirmaCariHesapOdemes
    
                                  group Odeme by new { Odeme.FirmaID } into oo
    
                                  select new { FirmaID = oo.Key.FirmaID, odenen = oo.Sum(odeme => odeme.OdemeTutari) } into o
    
                                  from Hesap in db.tblFirmaCariHesaps
    
                                  where Hesap.FirmaID == o.FirmaID
    
                                  select new { Hesap.FirmaID, Hesap.ToplamTutar, o.odenen } into HO
    
                                  group HO by new { HO.odenen, HO.FirmaID } into f
    
                                  select new { FirmaID = f.Key.FirmaID, ToplamBorç = f.Sum(s => s.ToplamTutar), Ödenen = f.Key.odenen, Kalan = f.Sum(s => s.ToplamTutar) - f.Key.odenen }
    
                                  ).ToList();
    
                }
    

    The result:

    For sql statement in Linq, you can have a check link below:

    http://msdn.microsoft.com/en-us/vstudio/bb688088.aspx

    It shows a lot detail examples.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 3, 2013 6:56 AM
    Moderator