none
Linq query Left join not returning all row from left table RRS feed

  • Question

  • I have two tables, StoreGroups:

    CREATE TABLE [dbo].[StoreGroups](
    	[GroupName] [nvarchar](30) NOT NULL,
    	[Store] [int] NOT NULL,
    	[Client] [smallint] NOT NULL CONSTRAINT [DF_StoreGroups_Client]  DEFAULT ((1)),
     CONSTRAINT [PK_StoreGroups] PRIMARY KEY CLUSTERED 
    (
    	[GroupName] ASC,
    	[Store] ASC,
    	[Client] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    

    and CalculationDetail

    CREATE TABLE [dbo].[CalculationDetail](
    	[Client] [smallint] NOT NULL CONSTRAINT [DF_CalculationDetail_Client]  DEFAULT ((1)),
    	[Report] [varchar](20) NOT NULL,
    	[ID] [varchar](50) NOT NULL,
    	[Store] [int] NOT NULL,
    	[YYYYMMDD] [varchar](8) NOT NULL,
    	[Amount] [money] NULL,
    	[Text] [varchar](100) NULL,
    	[BIZDATE]  AS ([yyyymmdd])
    ) ON [PRIMARY]

    In the query below, I first join the StoreGroups to calculationdetail for this years sales and then join in the calculationdetail table again to get last years sales. I"m trying to use a left join to get all rows on the first join even when there are no sales for the last year for a store.  Howver, I'm only getting rows for store that have sales for a year ago. (i have a few random values hard coded in for testing) I'm at a loss for where I'm going wrong.

    StoreGroups.Where(sg=>sg.GroupName=="All Open")
    .Join(CalculationDetails, sg => new{sg.Store, ID = "NET1"},cd => new{cd.Store, cd.ID}, (sg,cd) => new{cd.Store, cd.YYYYMMDD, cd.ID, cd.Amount})
    .Where(cd=>cd.YYYYMMDD.CompareTo("20120410")>=0 && cd.YYYYMMDD.CompareTo("20120410") <= 0)
    .Join(CalculationDetails, cd=> new {cd.Store,cd.ID,}, cd2=> new {cd2.Store,cd2.ID} == null ? null:new {cd2.Store,cd2.ID}, (cd,cd2) => new{cd.Store,cd.YYYYMMDD,compDate = cd2.YYYYMMDD,cd.Amount,compAmount = cd2.Amount })
    .Where(cd => Convert.ToDateTime(cd.YYYYMMDD).AddDays(-364) ==  Convert.ToDateTime(cd.compDate) || Convert.ToDateTime(cd.compDate) == null)
    .DefaultIfEmpty()
    .GroupBy(cd=>cd.YYYYMMDD)
    .Select(rs=>new { yyyymmdd = rs.Key, amount = rs.Sum(r=>r.Amount), compAmount = rs.Sum(r=>r.compAmount)  })


    Sunday, May 20, 2012 4:45 AM

All replies

  • Hi DavidGerler,

    Welcome to MSDN Forum.

    Based on the code, does the Linq to Entities you have posted can be executed? I create the tables and execute the Linq to Entities, there's an exception being thrown. By the way, you've used so many C# method in the query statement, Linq to Entities will not recognized the C# methods such as 'AddDays'.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, May 21, 2012 5:17 AM
    Moderator
  • Hi DavidGerler,

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

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Thursday, May 24, 2012 3:32 AM
    Moderator