I have a ProdFinSize table that has Range, Color, Model, Scale, Size, Rise, Inseam columns on it and the data type for the Inseam column is char(2) and has a default value of two spaces.
I wrote this SQL query to test the results I wanted.
SELECT *
FROM ProdFinSize
WHERE Range = '020'
and Color = '05'
and Model = 'JU'
and Scale = '1'
and Size = '34'
and Rise = 'R'
and inseam = '';
When I execute this, it returns a single item as it should.
Next I implemented the same thing using LINQ to Entities in an ASP.NET WebAPI
Here is my api function call
// GET /api/inventory/stockcheck
[Route("CheckStock")]
[HttpGet]
public IHttpActionResult CheckStock(string range, string color, string model, string scale,
string size, string rise, string inseam = "")
{
try
{
ProdFinSize product = null;
product = berle.ProdFinSizes.Single(
item =>
item.Range == range &&
item.Color == color &&
item.Model == model &&
item.Scale == scale &&
item.Size == size &&
item.Rise == rise &&
item.Inseam == inseam
);
// check if we found a matching product
if (product != null)
{
// see if the item is in-stock
var available = (product.FinSzOnHand-product.FinSzAloc-product.FinSzRlsd) > 0;
return Ok(available.ToString());
}
return Ok("False");
}
catch(Exception e)
{
return Ok(e.Message);
//return Ok("Item not found");
}
}
Now when I make the api call and pass the same values that I used in the SQL query, it returns a message: Sequence Contains No Elements.
Can somebody please tell me what I'm missing and why the same query in LINQ to Entities does not return the single result, as it should?
I'm new to LINQ to Entities, so I'm not sure if I'm doing something wrong.
Note I do not have the ability to change any of the columns or column data types on the database table.
When I run SQL Profiler, the query below gets executed.
exec sp_executesql N'SELECT TOP (2)
[Extent1].[CompanyCode] AS [CompanyCode],
[Extent1].[Range] AS [Range],
[Extent1].[Color] AS [Color],
[Extent1].[Model] AS [Model],
[Extent1].[Scale] AS [Scale],
[Extent1].[Inseam] AS [Inseam],
[Extent1].[Size] AS [Size],
[Extent1].[Rise] AS [Rise],
[Extent1].[FinSzProd] AS [FinSzProd],
[Extent1].[FinSzOnHand] AS [FinSzOnHand],
[Extent1].[FinSzXfrIn] AS [FinSzXfrIn],
[Extent1].[FinSzXfrOut] AS [FinSzXfrOut],
[Extent1].[FinSzRetn] AS [FinSzRetn],
[Extent1].[FinSzShip] AS [FinSzShip],
[Extent1].[FinSzRsrvd] AS [FinSzRsrvd],
[Extent1].[FinSzRlsd] AS [FinSzRlsd],
[Extent1].[FinSzAloc] AS [FinSzAloc],
[Extent1].[DateTimeStamp] AS [DateTimeStamp],
[Extent1].[FormID] AS [FormID],
[Extent1].[UserID] AS [UserID],
[Extent1].[rowguid] AS [rowguid]
FROM (SELECT
[ProdFinSize].[CompanyCode] AS [CompanyCode],
[ProdFinSize].[Range] AS [Range],
[ProdFinSize].[Color] AS [Color],
[ProdFinSize].[Model] AS [Model],
[ProdFinSize].[Scale] AS [Scale],
[ProdFinSize].[Inseam] AS [Inseam],
[ProdFinSize].[Size] AS [Size],
[ProdFinSize].[Rise] AS [Rise],
[ProdFinSize].[FinSzProd] AS [FinSzProd],
[ProdFinSize].[FinSzOnHand] AS [FinSzOnHand],
[ProdFinSize].[FinSzXfrIn] AS [FinSzXfrIn],
[ProdFinSize].[FinSzXfrOut] AS [FinSzXfrOut],
[ProdFinSize].[FinSzRetn] AS [FinSzRetn],
[ProdFinSize].[FinSzShip] AS [FinSzShip],
[ProdFinSize].[FinSzRsrvd] AS [FinSzRsrvd],
[ProdFinSize].[FinSzRlsd] AS [FinSzRlsd],
[ProdFinSize].[FinSzAloc] AS [FinSzAloc],
[ProdFinSize].[DateTimeStamp] AS [DateTimeStamp],
[ProdFinSize].[FormID] AS [FormID],
[ProdFinSize].[UserID] AS [UserID],
[ProdFinSize].[rowguid] AS [rowguid]
FROM [dbo].[ProdFinSize] AS [ProdFinSize]) AS [Extent1]
WHERE ([Extent1].[Range] = @p__linq__0) AND (@p__linq__0 IS NOT NULL) AND ([Extent1].[Color] = @p__linq__1) AND (@p__linq__1 IS NOT NULL) AND ([Extent1].[Model] = @p__linq__2) AND (@p__linq__2 IS NOT NULL) AND ([Extent1].[Scale] = @p__linq__3) AND (@p__linq__3 IS NOT NULL) AND ([Extent1].[Size] = @p__linq__4) AND (@p__linq__4 IS NOT NULL) AND ([Extent1].[Rise] = @p__linq__5) AND (@p__linq__5 IS NOT NULL) AND ([Extent1].[Inseam] = @p__linq__6) AND (@p__linq__6 IS NOT NULL)',N'@p__linq__0 varchar(8000),@p__linq__1 varchar(8000),@p__linq__2 varchar(8000),@p__linq__3 varchar(8000),@p__linq__4 varchar(8000),@p__linq__5 varchar(8000),@p__linq__6 varchar(8000)',@p__linq__0='020',@p__linq__1='05',@p__linq__2='JU',@p__linq__3='1',@p__linq__4='34',@p__linq__5='R',@p__linq__6=NULL
