Linq Query returns 0 results, but should return 1

Question

I have a SQL Server Compact Edition version 3.5 for a winforms app that is used as to store order information, but I have had a complaint from someone who is having issues where the detail isn't showing up after it has been finished and submitted, and by receiving the database of the user I can reproduce the problem, but I cannot figure out why. What I'm trying to figure out is why they aren't syncing up, because others do work just fine, and I have searched Stack Overflow and not found an answer that has worked for me.

The results of linqpad/SSMS against the database I received are the same and bring up one row as a result of the following query (same query as entity framework profiler shows):

SELECT [Extent1].[Id]                  AS [Id],
       [Extent1].[OrderHeaderId]       AS [OrderHeaderId],
       [Extent1].[Price]               AS [Price],
       [Extent1].[Quantity]            AS [Quantity],
       [Extent1].[OverridePrice]       AS [OverridePrice],
       [Extent1].[ShippingWeight]      AS [ShippingWeight],
       [Extent1].[ExtendedPrice]       AS [ExtendedPrice],
       [Extent1].[OrderId]             AS [OrderId],
       [Extent1].[ProductItemNo]       AS [ProductItemNo],
       [Extent1].[ProductItemNoTypeId] AS [ProductItemNoTypeId]
FROM   [OrderDetail] AS [Extent1]
WHERE  [Extent1].[OrderHeaderId] = 'eec06164-a052-4c23-9575-8fe1b80c8baa' /* @p__linq__0 */

However, if I use either of the following statements I get no results:

_orderDetails = ctx.OrderDetail.Where(o => o.OrderHeaderId == _order.Id).ToList();

_orderDetails = (from od in ctx.OrderDetail
                 where od.OrderHeaderId == _order.Id
                 select od).ToList();

Order Header:

-- Creating table 'OrderHeader'
CREATE TABLE [OrderHeader] (
    [Id] uniqueidentifier  NOT NULL,
    [PONumber] nvarchar(4000)  NULL,
    [InternalOrderText] nvarchar(4000)  NULL,
    [TimeStamp] datetime  NOT NULL,
    [IOCTrackingNo] nvarchar(4000)  NULL,
    [CarrierCode] nvarchar(4000)  NULL,
    [HowEntered] nvarchar(4000)  NOT NULL,
    [MessageName] nvarchar(4000)  NOT NULL,
    [RepID] nvarchar(4000)  NOT NULL,
    [BatchID] nvarchar(4000)  NOT NULL,
    [Status] nvarchar(4000)  NOT NULL,
    [ApplicationVersion] nvarchar(4000)  NOT NULL,
    [CustomerAccountID] nvarchar(4000)  NOT NULL,
    [CustomerSubAccountID] nvarchar(4000)  NOT NULL,
    [SystemArrivalDate] datetime  NULL,
    [OrderId] int  NOT NULL
);
GO

-- Creating primary key on [Id] in table 'OrderHeader'
ALTER TABLE [OrderHeader]
ADD CONSTRAINT [PK_OrderHeader]
    PRIMARY KEY ([Id] );
GO

Order Detail:

-- Creating table 'OrderDetail'
CREATE TABLE [OrderDetail] (
    [Id] uniqueidentifier  NOT NULL,
    [OrderHeaderId] uniqueidentifier  NOT NULL,
    [Price] decimal(18,2)  NOT NULL,
    [Quantity] int  NOT NULL,
    [OverridePrice] bit  NOT NULL,
    [ShippingWeight] decimal(18,3)  NOT NULL,
    [ExtendedPrice] decimal(18,2)  NOT NULL,
    [OrderId] int  NOT NULL,
    [ProductItemNo] nvarchar(4000)  NOT NULL,
    [ProductItemNoTypeId] uniqueidentifier  NOT NULL
);
GO

-- Creating primary key on [Id] in table 'OrderDetail'
ALTER TABLE [OrderDetail]
ADD CONSTRAINT [PK_OrderDetail]
    PRIMARY KEY ([Id] );
GO

Foreign Key:

-- Creating foreign key on [OrderHeaderId] in table 'OrderDetail'
ALTER TABLE [OrderDetail]
ADD CONSTRAINT [FK_OrderDetailOrderHeader]
    FOREIGN KEY ([OrderHeaderId])
    REFERENCES [OrderHeader]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_OrderDetailOrderHeader'
CREATE INDEX [IX_FK_OrderDetailOrderHeader]
ON [OrderDetail]
    ([OrderHeaderId]);
GO

Did you check if _order.Id is what you expect it to be which is 'eec06164-a052-4c23-9575-8fe1b80c8baa'? — neo, May 28 '13 at 20:14
Yes I did, that's how I originally got the ID was by viewing the order while debugging. Guess I should post some code too. — Charles380, May 28 '13 at 20:26
Could you try this? ctx.OrderDetail.Where(o => o.OrderHeader.Id == _order.Id).ToList(); — neo, May 28 '13 at 20:28
Already tried that it's buried in the quite lengthy question. — Charles380, May 28 '13 at 20:36
Why do you have your id fields as both unique identifier and primary key? Primary key already guarantees that it's unique. One time I was experimenting with linq to sql. I created a dummy table and I was writing queries like yours and I wasn't getting any results. Then I realized that I didn't create any primary key. And linq to sql requires you to have primary key on the tables you query. Your case might be related. — neo, May 29 '13 at 13:23
With sql ce you cannot use int identity fields, you have to generate them and the general rule with sql ce seems to be to use GUIDs that are generated in code. Id is the primary key for the OrderDetail field, so when a line is deleted I know which one is actually deleted, the OrderHeaderId is a foreign key to the OrderHeader table — Charles380, May 29 '13 at 14:20

score 0 · Accepted Answer · answered Jun 06 '13 at 14:03

0

Myself and several others weren't able to figure out why the link became broken, so I decided to change the database to SQL CE 4.0 because of the ability to use int identity fields, and we haven't had this issue since.

answered Jun 06 '13 at 14:03

Charles380

1,269
8
19

Linq Query returns 0 results, but should return 1

1 Answers1