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?

Answer 1

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.