fransbouma / linqtosql2 Goto Github PK

Entity splitting is sometimes a nice feature if you want to model a part of an entity into another entity. Typical example is to model the Photo field (which is an Image field) in an Employee entity into a separate entity, e.g. EmployeePhoto, with a 1:1 relationship over the PK. This allows the Employee entity to not fetch the Photo field by default, only when the related entity is fetched as well.

The implementation is a little tricky because persisting a new EmployeePhoto entity isn't an insert but an update of an existing row. I.o.w.: the entity type has to be known as a 'split off' entity so the core logic knows to generate an update, not an insert.

(related to #21)

(requires #6 and #8 to be implemented)

Add Oracle support for DML queries. If these work, add Oracle support for DDL queries, however this is has no high priority at the moment.

Borrow from the Oracle DQE sourcecode in LLBLGen Pro Runtime.

Currently usage of SqlGeography etc. is not supported. This is not that hard to add.

Add support for sequences to be specified as sources for identity fields in the mapping meta data. SQL Server 2012+, Oracle and other databases require this support.

Similar to #16 Delete by Query but let you update instead. See that issue for more info.

I'm not going to suggest a syntax here, just show what EFUtilities does.

EFBatchOperation.For(db, db.Comments).Where(x => x.Text == "a").Update(x => x.Reads, x => x.Reads + 1)

Once again the biggest problem is that aliases for joined/subqueried tables is more complicated in updates than selects.

See e.g. : http://stackoverflow.com/q/2555206

It's a bit of a problem now as an entity with fields which are not initialized (so are null), will get a value inserted (null) so defaults don't work.

It's not an ideal situation, fields which are not nullable but aren't initialized with a value now will not get the default set so the user has to specify a value in the client, which might differ from the value in the default constraint. Unfortunately with POCOs this isn't always determinable. Try to find a way to see whether a property has been set in the poco and use that info to determine whether a field has to be inserted.

Add an Async API to the main API for the general methods expected to be async, like ToListAsync() and the like. The methods to make Async can be borrowed from the LLBLGen Pro runtime framework.

Requires refactoring of internals to avoid clones all over the place.

(Related to #20) It might be required to exclude some fields from a fetch, e.g. because these fields are very big (data wise) and not needed for the use case the data is obtained.

typical syntax:

var employees = ctx.Employees.Where(e=>e.City=="The Hague").ExcludeFields(e=>e.Photo);

ExcludeFields only works on the final projection as it otherwise has little effect.

To avoid long lists of fields to exclude, an IncludeFields is added too, it allows the user to specify only the fields required.

PK and FK fields are always fetched and not excludable, to make sure graph management of the entities is preserved.

Something similar to:

db.SetX.DeleteWhere(b => b.Created < limit && b.Title == "T2.0");

The code to generate the filter is already there. The tricky part is that delete is a bit more complicated with joins than select is. If there are any local entities (do you have those in L2S?) that match the predicate I guess they should be deleted too.

In EFUtilities I decided to have this kind of operations outside the DbContext because they are "power functions" + work differently from the normal workflow. Not sure what would be best.

The reference source hasn't been shipped with any tests, and Microsoft isn't going to provide any tests as well (rumor has it Linq to Sql has over 15 million tests). It might be a good start to port over some of the unit tests from LLBLGen Pro to have a set of basic tests to cover the basics.

Add proper prefetch path/ eager loading functionality to surpass the limited LoadWith() functionality currently in linq to sql as LoadWith only loads 1 level deep and falls back to lazy loading for further levels.

Borrow API from LLBLGen Pro Linq provider's WithPath system, like this one: https://github.com/SolutionsDesign/LLBLGen.Linq.Prefetch

Add the ability to specify a TypeConverter derived class for a mapping to be used at runtime to convert values from/to different types when reading from /writing to the DB. Supply default system type converters to make it easier to specify conversions (e.g. to convert from guid to string, "Y/N" to bool etc.)

When writing extensions for EF it's actually incredibly hard to find out what DB engine is being used. You end up checking the connection which is a problem if that is wrapped because of things like profiling. (Don't get me started on what I feel about the IConnection design)

Simply being able to get the info about the db or the provider being used would solve this.

At the moment when a new context is created and the first query is executed, it will perform 3 open/close operations on a connection before it will execute the real query. This is for determining the db version, but it's not needed as the user should be able to configure whether the datasource is sqlserver or ce or another db type (e.g. a compatibility mode).

CE is phased out anyway, so this is something that hurts every user but for no real gain.

To make it possible to support multiple database types, the sql server specific code has to be refactored into a separate assembly. Make sure it builds on top of a series of base classes as a lot of the code likely will be re-used by providers for other DBs

It's now left up to the user to cache the meta-data and we can do that ourselves with a static resource very easily. This allows faster context creation and thus faster queries without any effort.

Not sure how L2S worked here to start with. I just know how horrible that was in earlier versions of EF. It's much easier if I as an extension author can just get something like the following for each column.

{ Code : { PropertyInfo, (Maybe info about navigation prop) } Sql : { ColumnName, SettingsLikeNullableEtcEtc } ,

There's no provider directive for SQL Server 2012 or higher in Linq to SQL. When these are added we can add features specific for SQL Server 2012 and higher.

(requires #3 being implemented)

SQL Server 2012 introduces a new, simpler way to do paging. This should be added.

Currently there's no NEWSEQUENTIALID() support in the runtime. Does require query batching, which might not be present as well, as the value inserted has to be read using a scalar query appended to the insert query, and propagated to the entity being inserted.

In the LLBLGen Pro query engines we don't use any ADO.NET specific namespaces and the db engines in Linq to Sql2 should not depend on any external namespace. This requires additional code, especially ADO.NET specific type setters on DbCommand instances, in the form of IL generated delegates. This code is available in the llblgen pro runtime and we can just copy over these classes to make the Linq to Sql 2 code independent of any ado.net provider namespace.

This is required to be done before any other major work as code can be made much more db independent because of this.

This test fails

[Test]
public void PhantomInsertPreventionTest()
{
    var newCustomer = new Customer();
    var ba = new Address();
    var va = new Address();
    newCustomer.VisitingAddress = va;
    newCustomer.BillingAddress = ba;

    // as everything is empty, and Address isn't saved because it's not dirty, customer shouldn't be saved as well.
    using(var ctx = GetContext())
    {
        ctx.Customers.InsertOnSubmit(newCustomer);
        ctx.SubmitChanges();
        Assert.AreEqual(0, ba.AddressId);
        Assert.AreEqual(0, va.AddressId);
    }
}

because Linq to Sql happily inserts Address, while it's not dirty and has nonnullable fields. No save should occur at all, as there are only empty entities to persist. Preventing these 'phantom' inserts will avoid the user running into either empty rows (because there were nullable fields so the persist succeeded), or have to check up front whether a save is necessary.

One cool thing EF7 will do is to use OUTPUT in Sql Server to support batch inserts even with db generated ids. I have not investigated if any other engines support it or have similar.

Compared to SqlBulkCopy it's ofcourse slower, but the advantage is that it supports FK fixup automatically ang gives better error messages and might even be faster under a certain treshold. It already needs code to decide in which order a graph should be inserted so should be a quite easy implementation.