Trouble with checking for null in a SQL Compact qu

问题:

I'm working through the SportsStore example from Pro ASP.NET MVC 3 Framework. At the beginning of chapter 8 i'm instructed to edit my ProductController class, adding the .Where line as follows:

    public ViewResult List(string category, int page = 1)
    {
        ProductsListViewModel viewModel = new ProductsListViewModel
        {
            Products = repository.Products
                .Where(p => category == null || p.Category == category)
                .OrderBy(p => p.ProductID)
                .Skip((page - 1) * PageSize)
                .Take(PageSize),
            PagingInfo = new PagingInfo
            {
                CurrentPage = page,
                ItemsPerPage = PageSize,
                TotalItems = repository.Products.Count()
            },
            CurrentCategory = category
        };
        return View(viewModel);
    }

When I run the code i get the following error:

Exception Details: System.Data.SqlServerCe.SqlCeException: The specified argument
value for the function is not valid. [ Argument # = 1,Name of
function(if known) = isnull ]

on the foreach line in the following code block:

@model SportsStore.WebUI.Models.ProductsListViewModel

@{
    ViewBag.Title = "Products";
}

@foreach (var p in Model.Products)
{
    Html.RenderPartial("ProductSummary", p);
}
<div class="pager">
    @Html.PageLinks(Model.PagingInfo, x => Url.Action("List", new {page = x}))
</div>

I've searched a good bit and found a lot of references to this StackOverflow post in multiple places, but changing the query to

.Where(p => category == null ? true : p.Category == category)

had no effect.

Some basic information:

• This is an MVC3 project using the Razer view engine and C#.
• All of the items in my SQL Compact 4.0 database have a category.
• Commenting out the category == null bit makes the code run just fine.
• The second version I gave above is what is in the downloadable source code from their site.

It does work without the null checking, but i'm worried that if I just move on i may run into issues later on. Does anyone have any ideas as to how I can fix it?

回答1:

I think the problem is that the LINQ query is being deferred until it gets to the SQL server. My guess is that the SQL compact server has an issue with checking category == null.

Try using a non-deferred LINQ method before calling the Where method. Something like

Products = repository.Products.ToList()
    .Where(p => category == null || p.Category == category)
    .OrderBy(p => p.ProductID)
    .Skip((page - 1) * PageSize)
    .Take(PageSize);

回答2:

Same problem, but the marked answer doesn't work for me because my WHERE clause is on a related table that I do not want to return in the results. Also, returning everything and then doing the .Where() afterwards is very inefficient.

Here's a better way:

SQL CE cannot handle parameters so, the so standard construction

.Where(p => p.Category == category || category == null)

fails because LINQ needs to use a parameter to do that value comparison in-query. But it is perfectly valid SQL to pass something like this:

.Where(p => p.Category == category || true)

because 'true' is always...well...true. So if you parse your variable to a bool before the LINQ statement and then pass that variable to SQL CE, you're in good shape:

bool bCategory = (string.IsNullOrEmpty(category)) ? true : false;

Products = repository.Products.Where(p => p.Category == category || bCategory);

This allows you do to all the filtering in the query before returning data, and you can use this trick on as many conditions as you want without it getting messy.

回答3:

Not very sure if it gives the same output, I think it should, have a look on this...

 Products = repository.Products
            .Where(p => p.Category == null || p.Category == category)
            .OrderBy(p => p.ProductID)
            .Skip((page - 1) * PageSize)
            .Take(PageSize),