LINQ query is null

Question

I am building a WinForms application that searches for books in the Database.

I have four tables: Author,Book,Country,Genre;

There is a field in "Book" table called "Year". I added startDate and endDate fields, so it can search for specific book released between the given two years(startDate and endDate both are integers). That is where my troubles began.

This is how I parse inputs.

int startDateResult = 0;
int? startDate = null;

if (inputStartDate == string.Empty)
{
    startDate = null;
}
else
{
    if (Int32.TryParse(inputStartDate, out startDateResult))
    {
        startDate = startDateResult;
    }
    else throw new Exception("Please input correct year");
}

int endDateResult = 0;
int? endDate = null;

if (inputEndDate == string.Empty)
{
    endDate = null;
}
else
{
    if (Int32.TryParse(inputEndDate, out endDateResult))
    {
        endDate = endDateResult;
    }
    else throw new Exception("Please input correct year");
}

This is the LINQ query I am using for searching.

specificBookForAuthor = _context.Books.Where(c =>            
    (c.Author.Name.Contains(First) || c.Author.Surname.Contains(Last))

    && book==string.Empty?true: c.Name.Contains(book)

    && country == string.Empty ? true : c.Author.Country.Name.Contains(country)

    && genre == string.Empty ? true : c.Genre.Name.Contains(genre)

    && inputYear == string.Empty ? true : c.Year==year

    && inputStartDate == string.Empty ? true : c.Year >= startDate

    && inputEndDate == string.Empty ? true : c.Year <= endDate

).Select(b => b).ToList();

This query did not work. Then, I tried to comment all the lines except "inputEndDate", typed 0 in startDate and 5000 in endDate. After Debug, found out, that "specificBookAuthor"-s count was 1. Which was CORRECT.

Commented Code :

specificBookForAuthor = _context.Books.Where(c =>

    (c.Author.Name.Contains(First) || c.Author.Surname.Contains(Last))

    //&& book==string.Empty?true: c.Name.Contains(book)

    //&& country == string.Empty ? true : 
    //    c.Author.Country.Name.Contains(country)

    // && genre == string.Empty ? true : c.Genre.Name.Contains(genre)

    // && inputYear == string.Empty ? true : c.Year==year

    // && inputStartDate == string.Empty ? true : c.Year >= startDate

    inputEndDate == string.Empty ? true : c.Year <= endDate

).Select(b => b).ToList();

Did the same with inputStartDate(commented inputEndDate line and uncommented inputStartDate). Worked fine.

I get the problem when I leave both of the fields uncommented. Like this:

specificBookForAuthor = _context.Books.Where(c =>

    (c.Author.Name.Contains(First) || c.Author.Surname.Contains(Last))

    //&& book==string.Empty?true: c.Name.Contains(book)

    //&& country == string.Empty ? true :c.Author.Country.Name.Contains(country)


    // && genre == string.Empty ? true : c.Genre.Name.Contains(genre)

    // && inputYear == string.Empty ? true : c.Year==year

    && inputStartDate == string.Empty ? true : c.Year >= startDate

    && inputEndDate == string.Empty ? true : c.Year <= endDate

).Select(b => b).ToList();

In that case, "specificBookAuthor"-s count is NULL instead of 1.

You need to debug this. Cut the problem in half, test the UI by entering your data and seeing what you get (using Debug.WriteLine or MessageBox.Show). Then debug the query by fixing your inputs and doing what you started doing (commenting out parts) and figuring what is failing as you add in the commented out code a bit at a time. — Flydog57
– Flydog57, Commented Oct 14, 2018 at 18:28
you can use EF Profiler or Sql Profiler (if use sql server) and see generated query to find the problem quickly — Hamed Naeemaei
– Hamed Naeemaei, Commented Oct 14, 2018 at 18:30
@Flydog57 I did debug it. Returns correct data when I leave only one field(either inputStartDate or inputEndDate) it works fine. Couldn't see why is null returned — Guga Todua
– Guga Todua, Commented Oct 14, 2018 at 18:36
What type of LINQ are we talking about? Also, why don't you test if startDate and endDate are null instead of the input strings? In other words, the first part of your question could be removed, it's not relevant. — Gert Arnold
– Gert Arnold, Commented Oct 14, 2018 at 19:10

Guillermo Gerard · Accepted Answer · 2018-10-14 21:34:51Z

2

Could you test the following change which may not be directly related to the issue, but it can give you a more readable/debugable code?

Rewrite your code to this:

specificBookForAuthor = _context.Books.Where(c => 
    (c.Author.Name.Contains(First) || c.Author.Surname.Contains(Last)));

if(!string.IsNullOrEmpty(book))
    specificBookForAuthor = specificBookForAuthor.Where(c => c.Name.Contains(book));

if(!string.IsNullOrEmpty(country))
    specificBookForAuthor = specificBookForAuthor.Where(c => c.Author.Country.Name.Contains(country));

//....and so on with your other conditions;
//....finally:

specificBookForAuthor = specificBookForAuthor.ToList();

As I said, this may not resolve your problem, but this way you can properly debug condition by condition and maybe then you can find the problem.

edited Oct 14, 2018 at 21:34

answered Oct 14, 2018 at 18:41

Guillermo Gerard

92210 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Harald Coppoolse · Accepted Answer · 2018-10-15 13:29:28Z

So every Book has a property Year, representing the year in which a book was published.

Furthermore you have two nullable integers startDate and endDate and you want to limit the selected books to books published between startDate and endDate, with special cases if one or both of these values are null.

You have an input IQueryable<Book> and as output you want the query that will only fetch the books between startDate and endDate, with special treatment if these values are null.

To keep it readable, testable and maintainable, I suggest an extension function. See Extension Methods Demystified

public static IQueryable<Book> WherePublishedBetween(this IQueryable<Book> books,
    Year? start,
    Year? end)
{
    if (start.HasValue)
    {
        if (end.HasValue)
            return books.Where(book => start.Value <= book.Year && book.Year <= end.Value);
        else
            return books.Where(book => start.Value <= book.Year);
    }
    else
    {   // no start value
        if (end.HasValue)
            return books.Where(book => book.Year <= end.Value);
        else
            return books;
    }
}

Usage:

int? startYear = ...
int? endYear = ...
var queryBooks = myDbContext.Books
    .Where(book => book.Author.Name.Contains(First)
                || book.Author.Surname.Contains(Last)
           && ... // check other properties)
    .WherePublishedBetween(startYear, endYear)

    // continue with the query, with whatever LINQ you need
    .Select(book => ...)

The LINQ expression builder will be smart enough to combine these two Where statements using Expression.AndAlso

Collectives™ on Stack Overflow

LINQ query is null

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related