Queryable with generic Type <T> Handling Null or 0 Values

Question

{"The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type."}

I am trying to handle null values in a Queryable List with Generic Type T. I have tried DefaultIfEmpty(0) does not work because cannot convert Type T to int. How do I handle nulls or ints To a list?

public  IEnumerable<T> SelectAll<T>(Object TableName)where T:class
{
    String TableNameString = Type.GetType(TableName.ToString()).Name;
            QueryString = "SELECT" + " " + "*" + " " + "FROM" + " " + TableNameString;
    if (Database.Connection.State == ConnectionState.Closed)
        Database.Connection.Open();

    var a = ((IObjectContextAdapter)this).ObjectContext;
    var t = a.ExecuteStoreQuery<T>(QueryString).AsQueryable().DefaultIfEmpty().ToList();
    List<T> newList = t;
    return newList;
}

How are you calling this method? What is the value of T in your specific case? is it int? — Yacoub Massad
– Yacoub Massad, Commented Jul 1, 2016 at 14:00
I am calling this method within a class which converts the Type of the Class to T and returns a Listof<classtype> @YacoubMassad — Mark Evans
– Mark Evans, Commented Jul 1, 2016 at 14:05
If the database table allows nulls in the table, you need to handle that appropriately in the code. You could do this using some SQL in your QueryString. SELECT (prop1, prop2, prop3, ISNULL(prop4, 0), ISNULL(prop5, 'NULL') FROM table_name; where prop4 is an integer type and prop5 is a varchar type. Also, you could put the current query in a single string, QueryString = "SELECT * FROM " + TableNameString; — Ingenioushax
– Ingenioushax, Commented Jul 1, 2016 at 14:06

Derpy · Accepted Answer · 2016-07-05 14:19:39Z

(It) does not work because cannot convert Type T to int

It sounds like you need to pass in the type int? but the compiler will not allow it because of the generic parameter constraint. Can you remove this constraint from your method?

where T : class

I don't believe DefaultIfEmpty(0) does what you think it does. For one, it won't take effect until after the failed mapping to type T. Secondly, it doesn't replace null elements with 0. It replaces an empty result set with a result set with exactly one item valued 0.

Here is an example of nullable mappings and how DefaultIfEmpty works.

private void MappingExample()
{
    var complex = GetQuery<ComplexObject>("select Id, null as OptionalAlias from customer").ToArray();
    var simple = GetQuery<int?>("select null as OptionalAlias from customer").ToArray();
}

private void DefaultIfEmptyExample()
{
    var nulls = new int?[] { null, null };
    var nullsWithDefault = simple.DefaultIfEmpty(0); //still full of nulls

    var empty = new int?[] { };
    var emptyWithDefault = empty.DefaultIfEmpty(0); //has one item valued 0
}

private class ComplexObject
{
    public int Id { get; set; }
    public int? OptionalAlias { get; set; }
}

private IEnumerable<T> GetQuery<T>(string sql)
{
    var context = ContextManager.GetNew(); //proprietary, replace with your own
    var parms = new System.Data.SqlClient.SqlParameter[] { };
    return context.Database.SqlQuery<T>(sql, parms);
}

Collectives™ on Stack Overflow

Queryable with generic Type <T> Handling Null or 0 Values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related