How to detect if column in SELECT can be null?

Question

I'm using DacFx at design-time to create a C# data layer that is generated from the sql files in an SSDT database project. See my SqlSharpener project on Github to see what I'm talking about.

I'm generating DTO objects that represent a row coming back from a SELECT statement of a stored procedure. To determine if a property on a DTO should be a nullable value type, I'm checking to see if the column is nullable in the table it came from. In case you're curious what that looks like, here's the code. (tSqlObject here is an instance of TSqlObject for the stored procedure)

var bodyColumnTypes = tSqlObject.GetReferenced(dac.Procedure.BodyDependencies)
    .Where(x => x.ObjectType.Name == "Column")
    .GroupBy(bd => string.Join(".", bd.Name.Parts))
    .Select(grp => grp.First())
    .ToDictionary(
        key => string.Join(".", key.Name.Parts),
        val => new DataType
        {
            Map = DataTypeHelper.Instance.GetMap(TypeFormat.SqlServerDbType, val.GetReferenced(dac.Column.DataType).First().Name.Parts.Last()),
            Nullable = dac.Column.Nullable.GetValue<bool>(val)
        },
        StringComparer.InvariantCultureIgnoreCase);

The problem is that I didn't account for left or right outer joins, which could also make the value null.

How can I determine if the column in a SELECT statement can be null? Or, if that question is too broad, how can I determine if the column came from a table that was left outer joined?

To be honest I don't think you can ever get this 100% correct. For just select statement you probably can but since this is intended to be a data layer how are you going to handle stored procedures? What if those procedures contain calls to other procedures or contain dynamic sql? — Sean Lange, May 14 '15 at 14:28
Do the tables not represent different objects? If so, then shouldn't your select return 2 separate objects (one of which can be null) instead of one object that represents all the fields in the query? — Kyle W, May 14 '15 at 14:40
@SeanLange, you bring up some good points. I haven't tested those scenarios yet. I think I could work through the nested procs problem. The dynamic sql one would probably be a lot trickier. But I don't think I need to solve every possible scenario for my project to be useful to people. I allow it to be extended by making all my classes partials and I'm adding `virtual` to all methods. This may be one of the exceptions where someone might have to code it manually, but they could still use the rest of the generated code. — adam0101, May 14 '15 at 15:09
@KyleW, I think I follow what you're saying, but even if I did change the implementation to group the columns into objects by the table they came from, the question remains of how to determine if those tables were left joined or not because I would still need to know whether to instantiate the object or not, no? — adam0101, May 14 '15 at 15:12
@adam0101 Well if a column is required in the table and is null in the result set (and all columns from that table are null) then the object should be null — Kyle W, May 14 '15 at 15:15
@KyleW, I see, so you are saying that if I generate the code in a way that at run-time it checks for null values, then I could return null for that object, but only after refactoring code to group columns into objects. And of course I'd have to handle cases where there is no column like when selecting a function or a literal. That would probably work, but would require a lot of rework to group the columns into objects. I'll keep it in mind if I can't find another solution. Thanks. — adam0101, May 14 '15 at 15:20

score 0 · Accepted Answer · answered May 14 '15 at 17:36

I think I found the answer. Given the QuerySpecification of the SELECT statement, I can get the list of joins using:

var joins = querySpecification.FromClause.TableReferences.OfType<QualifiedJoin>();

Each QualifiedJoin object has a QualifiedJoinType property which is an enumeration that will be set as Inner, LeftOuter, RightOuter, or FullOuter.

How to detect if column in SELECT can be null?

1 Answers1