check whether LINQ query returns rows

Question

I need to check whether a query returns rows and if it does, change it to a string, but if it doesn't, return "In Progress". I thought the below code would work but this is always true:

if (System.Linq.Enumerable.Count(columns) == 0)<--- always true but it shouldn't be

And when there isn't a row returned to columns I get the following error in my jQuery Ajax: "The cast to value type \u0027Int32\u0027 failed because the materialized value is null. Either the result type\u0027s generic parameter or the query must use a nullable type."

Here's my WebMethod:

using (dbPSREntities5 myEntities = new dbPSREntities5())
    {
        var thisId = myEntities.tbBreadCrumbs.Where(x => x.ProjectID == projectID && x.StatusID == statusID).Max(x => x.BreadCrumbID);
        var columns = myEntities.tbBreadCrumbs
            .Where(x => x.BreadCrumbID == thisId)
            .Select(x => x.CreateDateTime)
            .ToList();

        if (System.Linq.Enumerable.Count(columns) == 0)
        {
            var formattedList = columns
                .Select(d => null != d
                    ? d.Value.ToString("MMM dd, yyyy")
                    : string.Empty) // this is just one example to handle null
                .ToList();

            return formattedList;<-- return this if there is a BreadCrumbID (columns would have a row)
        }
        else
        {
            return "In Progress";<--- there was no BreadCrumbID (columns would have 0 rows)
        }

    }

Actually as someone else pointed out, his condition is backwards. It should be columns.Any(), you had it right the first time :) — Dmitriy Khaykin, Jan 24 '14 at 16:24

score 3 · Answer 1 · answered Jan 24 '14 at 16:21

3

You could use Any()

MSDN Enumerable.Any Method

answered Jan 24 '14 at 16:21

csteinmueller

2,427
1
21
32

Habib · Accepted Answer · 2014-01-24T16:28:13.230

2

You first check for Count == 0, doesn't sound right, I guess you need the opposite check. You should use Any or Count() > 0 check, Like:

if (columns.Any()) //Or columns.Count() > 0
{
    var formattedList = columns
        .Select(d => null != d
            ? d.Value.ToString("MMM dd, yyyy")
            : string.Empty) // this is just one example to handle null
        .ToList();

    return formattedList;<-- return this if there is a BreadCrumbID (columns would have a row)
}
else
{
    return "In Progress";<--- there was no BreadCrumbID (columns would have 0 rows)
}

You have to convert your List to string, in order for your method to return a string.

edited Jan 24 '14 at 16:28

answered Jan 24 '14 at 16:23

Habib

219,104
29
407
436

This worked great. Thanks. But I'm still getting the error:"The cast to value type \u0027Int32\u0027 failed because the materialized value is null. Either the result type\u0027s generic parameter or the query must use a nullable type." I think it's because thisId doesn't return any rows. Should I submit a new question about that or do you know how I conditionally check if thisID is NULL. – user1431633 Jan 24 '14 at 16:44
@user1431633, sure you can post a new question about it, but I guess the problem has to do with the some of your column being value type, thus you can't compare them with `null`, they should be `Nullable` or `DateTime?`. For more details see this question. http://stackoverflow.com/questions/6864311/the-cast-to-value-type-int32-failed-because-the-materialized-value-is-null – Habib Jan 24 '14 at 16:59

score 1 · Answer 3 · answered Jan 24 '14 at 16:21

1

Your condition is wrong. Count must be greater than zero

answered Jan 24 '14 at 16:21

Marko Juvančič

5,792
1
25
41

Maryam Arshi · Answer 4 · 2014-01-24T16:45:05.067

You only needs to ckeck .Count() > 0

if (columns.Count() > 0)
        {
            var formattedList = columns
                .Select(d => null != d
                    ? d.Value.ToString("MMM dd, yyyy")
                    : string.Empty) // this is just one example to handle null
                .ToList();

            return formattedList;<-- return this if there is a BreadCrumbID (columns would have a row)
        }
        else
        {
            return "In Progress";<--- there was no BreadCrumbID (columns would have 0 rows)
        }

Dmitriy Khaykin · Answer 5 · 2014-01-24T16:30:01.707

Use the .Any() method provided by List<T>:

If (columns.Any())
{
    // do your bidding
} else {
    // in progress code
}

Also your method has two different return signatures. That won't compile. You can't return a List or a string, unless the return type is object (not recommended).

I suggest to return a null list and check if it's null in your UI layer and then display the appropriate string or date values, since they end up as strings in the UI anyway.

check whether LINQ query returns rows

5 Answers5