I posted something about this a few years ago, but I'm wondering if it needs 'fixing' or if there's a reason it is the way it is.

Here's the code generated for a date field in a TypedView. The underlying database field is nullable, but the property type here is DateTime, so the only way to get Null in there is to call SetExpectedPaymentDateNull. This is extremely cludgy in code, particularly when populating many properties in a TypeViewRow. If the column is marked as Optional in the TypedView definition should this not be DateTime? with a conditional setter such as set { this[_parent.ExpectedPaymentDateColumn] = value ?? System.Convert.DBNull; } ?

I can use myNewRow["ExpectedPaymentDate"] = src.ExpectedPaymentDate ?? System.Convert.DBNull; in my data transfer process but I hate using the string identifier for the column name.

Cheers, Jason

        public System.DateTime ExpectedPaymentDate 
        {
            get { return IsExpectedPaymentDateNull() ? (System.DateTime)TypeDefaultValue.GetDefaultValue(typeof(System.DateTime)) : (System.DateTime)this[_parent.ExpectedPaymentDateColumn]; }
            set { this[_parent.ExpectedPaymentDateColumn] = value; }
        }

I recon this is a typed view based on a datatable (the original format we've generated typedviews since the beginning) ? The main reason this is done this way is to stay in sync with how typed datasets are doing this. This feature was designed in 2002, in the 'glory days' of typed datasets and we basically used that design for the typedview (and typed list) classes, as people would know what to do as typed datasets use the same pattern.

Nowadays no-one has even heard of typed datasets anymore of course but we kept the design because it would potentially break a lot of code if you change it. Instead we added an alternative for typed views, namely poco classes which are more in line with what people use nowadays. You can change to poco classes either at the project settings level, in the LLBLGen Pro Runtime Framework settings, or per typed view in their own settings, if you want to migrate away from the legacy setup. See for more info https://www.llblgen.com/Documentation/5.9/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/Adapter/Using%20TypedViews,%20TypedLists%20and%20Dynamic%20Lists/gencode_usingtypedview_adapter.htm#choosing-the-typed-view-class-type

Yes, it is indeed based on the DataTable, because amazingly enough the DataTable is the most efficient way I have found of pushing bulk data to SQL Server. I'm using a SqlBulkCopy instance to write tens of thousands of lines in a single call, and that takes a DataTable, array of DataRows, or DbDataReader/IDataReader as a parameter. Any structure supporting any of these interfaces will work fine, but the DataTable based TypedView obviously can be used without any modification. I can understand the desire to drop this antiquated feature (which I only ever use for this specific purpose) but is there an alternative process using the new POCO based implementation that doesn't require writing dozens of mapping systems to convert them to something SqlBulkCopy compatible?

Well it looks like this might work. I haven't tested it fully, but the idea looks sound. It's not ideal, as it uses reflection to populate the DataTable, but that's still relatively quick compared to the database write:

    public DataTable CreateDatatable<T>(IEnumerable<T> data) where T:class
    {
        DataTable datatable = new DataTable();
        var properties = typeof(T).GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Public | BindingFlags.Instance | BindingFlags.GetProperty);
        foreach (var p in properties)
        {
            DataColumn dc = new DataColumn(p.Name, Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType);
            datatable.Columns.Add(dc);
        }
        foreach (T row in data)
        {
            var newRow = datatable.NewRow();
            newRow.ItemArray = properties.Select(p => p.GetValue(row) ?? Convert.DBNull).ToArray();
            datatable.Rows.Add(newRow);
        }
        return datatable;
    }

This can be called with

            var testdata = db.typedListAccount.ToList();
            var dt = CreateDatatable<typedListAccountRow>(testdata);

So the only downsides of this approach are:

1. You must disable the CamelCase in the designer, because the column names don't match and you can't use ordinal positions because:

2. There is no underlying ordinal data associated with the properties in the POCO, so you have to write superfluous looking mapping values to get the BCPCopy to use names instead of the default positions.

For anyone wanting to use this method, here's an example of how you call the remote server:

    public void BulkUpload<T>(IEnumerable<T> dataArray, string destinationTableName) where T:class
    {
        var datatable = CreateDatatable(dataArray);
        using var bulkCopy = new SqlBulkCopy(_databaseAdapter.ConnectionString);
        foreach(DataColumn col in datatable.Columns)
            bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
        bulkCopy.DestinationTableName = destinationTableName;
        bulkCopy.WriteToServer(datatable);
    }

I think the mechanism we already have is sufficient too If you use the PocoWithQuerySpecQuery output type for the typedview, you get a poco and also you can fetch the query on the typedview as a datatable.

var qf = new QueryFactory();
var q = qf.Invoices
            .From(QueryTarget.InnerJoin(qf.Order)
                .On(InvoicesFields.OrderId.Equal(OrderFields.OrderId)))
            .Where(OrderFields.EmployeeId == 2);
var resultsAsDataTable = new DataAccessAdapter().FetchQueryAsDataTable(q);   

This will automatically build a datatable based on the projection in the query.

For Linq we don't have this mechanism, sadly. The advantage of the queryspec route is that you don't have to project the data first to poco instances, it's directly fetched into a datatable with columns based on the resultset. There's also an overload which accepts a pre-defined datatable if you want.

Yes, that would have been handy to know about before I wrote the Linq equivalent. I may switch it over at some point.

Cheers, Jason