schotime / npoco

Simple microORM that maps the results of a query onto a POCO object. Project based on Schotime's branch of PetaPoco

NPoco

Welcome to the NPoco! NPoco is a fork of PetaPoco based on Schotime's branch with a handful of extra features.

Getting Started: Your first query

public class User 
{
    public int UserId { get;set; }
    public string Email { get;set; }
}

IDatabase db = new Database("connStringName");
List<User> users = db.Fetch<User>("select userId, email from users");

This works by mapping the column names to the property names on the User object. This is a case-insensitive match.
There is no mapping setup needed for this (query only) scenario.

Checkout the Wiki for more documentation.

npoco's Issues

FetchBy<MyPoco>() doesn't return correct results when Select is used

Here's the scenario:

public class MyPoco
{
    public int ID { get; set; }
    public string Name { get; set; }
    // ... a bunch of other properties ...
}

Here's what I do if I'm interested in results containing the ID and Name only:

var list = db.FetchBy<MyPoco>(sql => sql
    .Select(x => new { x.ID, x.Name }));

This works fine, i.e. both the ID and the Name property of POCO's in the list are correctly populated.

However, if one of the properties is mapped to a DB column with a different name then that property is not populated. E.g., let's say the ID is mapped to a column named "Key" and Name to "Title", i.e.:

public class MyPocoMap : Map<MyPoco>
{
    public MyPocoMap()
    {
        Columns(x => 
        {
            x.Column(c => c.ID).WithName("Key");
            x.Column(c => c.Name).WithName("Title");
        }
    }
}

Then when I read the data using FetchBy like before, the list elements' ID and Name properties are all null. The SELECT statement generated by NPoco looks like this:

SELECT [Key] AS [ID], [Title] AS [Name] FROM MyPocos

And because the ID property is mapped to "Key" and Name is mapped to "Title", NPoco fails to populate these two properties because it cannot find those fields in the resultset.

The only way to trick NPoco is by doing this:

var list = db.FetchBy<MyPoco>(sql => sql
    .Select(x => new { Key = x.ID, Title = x.Name }));

Then SQL looks like so:

SELECT [Key], [Title] FROM MyPocos

and NPoco happily populates the properties.

However, it's not a good solution because it requires that the developer of the business logic layer must know the names of database fields mapped to POCO properties.

Any thoughts on how this could be fixed?

License

Please add licensing information to this project. Based on your nuget package, it's APL2, but you should have a license file in your repo.

LastSQL not set for Query()

Since the "Refactor preexecute for inserts" commit for dev2.0 LastSQL is not being set and OnExecutingCommand() is not being called when using Query().

The call to DoPreExecute() was removed from CreateCommand(). DoPreExecute() sets LastSQL and calls the OnExecutingCommand() hook. DoPreExecute() is now only called from ExecuteNonQueryHelper() and ExecuteScalerHelper() and not from any of the other Query() related functions.

Was this something missed in the refactor or are there additional changes coming for Query()?

Thanks,
Kurt

Snapshot.cs: Create a public getter/setter for memberWiseClone

I suggest you add a public getter/setter property for the trackedObject variable to make it easier to do partial Update.

Here's the use case for this:

public class Repository<T>
{
      public void Save<T>(T instance)
      {
            // Update an existing instance or insert a brand-new one.
            if (!Object.Equals(instance.ID, default(PK)))
            {
                // Seems that we are updating an existing instance.
                var existing = _db.SingleById<T>(instance.ID);
                if (existing == null)
                {
                    // Nope, no such instance exists, so insert it.
                    _db.Insert(instance);
                }
                else
                {
                    // Only update the fields that have actually changed.
                    var snapshot = _db.StartSnapshot(existing);
                    snapshot.ModifiedObject = instance;   // <-- the new property !!!
                    _db.Update(instance, snapshot.UpdatedColumns());
                }
            }
            else
            {
                _db.Insert(instance);
            }
       }
 }

Makes sense?

P.S. I love NPoco, great job!

FetchBy<T>() with OrderBy bug

Hi Adam,

I think there's a bug in this method:

        public List<T> FetchBy<T>(Func<SqlExpression<T>, SqlExpression<T>> expression)
        {
            var ev = _dbType.ExpressionVisitor<T>(this);
            var sql = expression(ev).Context.ToSelectStatement();
            return Fetch<T>(sql, ev.Context.Params.ToArray());
        }

When the expression contains OrderBy criteria, the criteria's parameters are lost because in the last line you're grabbing parameters from ev.Context rather than from the expression's lambda.

I believe the correct code should be:

        public List<T> FetchBy<T>(Func<SqlExpression<T>, SqlExpression<T>> expression)
        {
            var ev = _dbType.ExpressionVisitor<T>(this);
            var query = expression(ev);
            var sql = query.Context.ToSelectStatement();
            return Fetch<T>(sql, query.Context.Params.ToArray());
        }

If you agree I will submit a pull request.

Extension methods for table manipulation

Umbraco is using PetaPoco and they created some nice extensions for checking table presence in the database, creating new table, deleting table, bulk inserts,

Port these to NPoco. Since class property attributes are used, first inspect and modify attributes so they can completely describe property as a db column

Ignore() is ignored by FluentMapping

It looks like columns that are configured using the FluentMapping API to be ignored are included in the generated INSERT statement.

I have this class:

public class InvoiceHeader
{
    // A bunch of regular properties that are persisted ...

    // A child object property that should be ignored
    public virtual InvoiceAdditionalData AdditionalData { get; set; }

    // A child collection property that should be ignored
    public virtual ICollection<InvoiceLineItem> LineItems { get; set; }
}

In my FluentMapping configuration I do this:

FluentMappingConfiguration.Scan(scanner =>
{
    scanner.Assembly(typeof(IDocumentDatabase).Assembly);
    scanner.IncludeTypes(x => x.Namespace.StartsWith(typeof(InvoiceHeader).Namespace));
    scanner.Columns.IgnoreComplex();
    scanner.OverrideMappingsWith(new InvoiceHeaderMap());
}

I have confirmed by putting a breakpoint inside the IgnoreComplex lambda that it returns true for both AdditionalData and LineItems (i.e. they should be ignored).

However, the generated INSERT statement still contains both of these properties as if they were columns.

This also happens even if I explicitly Ignore() these properties in my InvoiceHeaderMap class.

FetchWhere(T) seemed not to work ... on Oracle (NPoco 2.2.40)

I tried to use FetchWhere(T).
but an error occoured.
(I use Oracle.DataAccess.Client. So error messege is {"ORA-01008:バインドされていない変数があります。"})

var list1 = db.FetchWhere＜ClassTest＞(x => x.Id >12 && x.Price >5);

I downloaded NPoco's source code, and try to inspect the error.
Finally I modified sql.cs as follows.

    public Sql(string sql, params object[] args)
    {
        _sqlFinal = _sql = sql;
        _argsFinal = _args = args;
}

And it works.

I don't sure if the modified code is right.
but I hope it is useful for your project.

Thank you for your greate project NPoco.

Timestamp/rowversion support

Is there any possibility of server based change tracking in addition to currently available client side counter?

With a table definition such as

[TableName("#Demo"), PrimaryKey("Id", AutoIncrement = true)]
public class Demo
{
    public int Id { get; set; }
    public string Value { get; set; }
    [VersionColumn(Rowversion)]     //change VersionColumn from true, false to None, Counter, Rowversion enum
    [VersionColumn, Generated]      //alternative: field is used for versioning, field is generated, it can be read but not written
    public byte[] TS {get; set; }
}

SqlServer database type could generate following SQL:

    Database db = new Database(params);
    db.Execute(@";
        CREATE TABLE #Demo
            (
            Id int NOT NULL IDENTITY (1, 1),
            Value varchar(50) NULL,
            TS timestamp NULL
            )  ON [PRIMARY]
        ");
    var demo = new Demo{Value = "Insert"};
    db.Insert(demo);
    /*
    -- Insert, update PK and TS values
    INSERT #Demo (Value) OUTPUT Inserted.Id, Inserted.TS VALUES ('Insert');
    */

    demo.Value = "Update";
    db.Update(demo);
    /*
    -- Update by PK and TS, reread TS value if successful or throw exception
    UPDATE #Demo SET Value='Update' OUTPUT Inserted.TS WHERE ID=@Id AND TS=@TS
    */

    db.Execute("DROP TABLE #Demo;")

I saw you already used OUTPUT to return identity value but then went back to SCOPE_IDENTITY() - was it because of performance?

Add DataTable methods

First of all, thank you for taking over PetaPoco and continuing the great work it started.

I am trying to use NPoco in the DL for a simple project, but have run into a few use cases where a DataTable would be much more useful. An example is exporting data to a CSV file. I understand that NPoco is designed for POCOs, but it would be a small change to add a DataTable method, while it would be a lot more difficult to build my own, including the factory/database agnostic pieces.

BTW: I tried to just extend the Database class, but some required pieces are marked "private", making extension impossible. The items I needed "protected" access to were CreateCommand() and _factory.

I can add this functionality if you accept pull requests.

[dev2.0] Insert/update/delete hooks (contexts)

Are these hooks needed if we have virtual Insert/Update/Delete methods with same data?

Publish symbol package to SymbolSource.org

I'm attempting to migrate an NHibernate site with an Oracle database to NPoco but have been running into a lot of issues. It would be helpful if I could easily attach the sources to the NPoco assembly to debug the issues (a lot of NPEs). I've not done it before, but it appears you can easily create and publish the symbol package using NuGet - http://docs.nuget.org/docs/creating-packages/creating-and-publishing-a-symbol-package.

Query<T>() parameterless overload

It is strange that Query method doesn't have parameterless overload like Fetch have.

public IEnumerable<T> Query<T>()
{
    return Query<T>("");
}

NPoco IQueryable provider?

Adam, have you ever considered creating a NPoco provider to support Linq's IQueryable interface? Your SqlExpression is a step in that direction but it doesn't actually map to IQueryable one-to-one.

Async operations using TPM

Any chance we get Async versions based on IOCP for Fetch, Single, etc. ?
E.g for SQL Server flavor something in the line of BeginExecuteReader.

Like: T t = await db.FetchAsync( sql );

Fluent Mapping confusion

I am trying to figure out how to do fluent mapping and have a couple of questions:

1. Your example on using a factory pattern to create a fluently mapped DB goes like this:

DbFactory = DatabaseFactory.Config(x =>
{
    x.UsingDatabase(() => new Database("connString");
    x.WithFluentConfig(fluentConfig);
    x.WithMapper(new Mapper());
});

Except that there's no Mapper class. So what exactly should I do here?

2. I have a few large POCO's with properties that mostly correspond 1-to-1 to DB columns except for a few that don't (i.e. different names or DB types). So I need to map most of them using convention-based mapping but override some properties' mappings. If I follow your examples and do this:

public class MyPocoMap : Map<MyPoco>
{
    public MyPocoMap()
    {
        Columns(x =>
        {
            x.Column(col => col.SomeProperty).WithName("SomeFieldName");
        });
    }
}

then would the rest of the POCO's properties be auto-mapped, or would I then have to explicitly map every single one of them?

Thanks!

Proposed change to UpdateContext

I am working on a project where I need to save a history of changes to entities of certain types in a separate table. Each historic change record should contain a list of changes made to individual fields, all concatenated together into a string.

In order to make it happen I want to override the OnUpdating method in my custom class derived from Database and build up a string of changes. However, the current UpdateContext code only stores a list of column names that are about to be updated, not their new values. Therefore I propose the following changes:

a) Move the Change class outside of Snapshotter<T>.

b) Modify the Changes property of UpdateContext to be a list of Changes, not strings, like so:

public class UpdateContext
{
    public UpdateContext(object poco, string tableName, string primaryKeyName, object primaryKeyValue, IEnumerable<Change> changes)
    {
        Poco = poco;
        TableName = tableName;
        PrimaryKeyName = primaryKeyName;
        PrimaryKeyValue = primaryKeyValue;
        Changes = changes;
    }
    public object Poco { get; private set; }
    public string TableName { get; private set; }
    public string PrimaryKeyName { get; private set; }
    public object PrimaryKeyValue { get; private set; }
    public IEnumerable<Change> Changes { get; private set; }
}

c) Modify the Database class to change the three Update methods that currently take a 'columns' parameter to take a 'changes' parameter instead, with the main Update method looking like so:

    public int Update(string tableName, string primaryKeyName, object poco, object primaryKeyValue, IEnumerable<Change> changes)
    {
        if (!OnUpdating(new UpdateContext(poco, tableName, primaryKeyName, primaryKeyValue, changes))) return 0;

        if (changes != null && !changes.Any()) return 0;

        var sb = new StringBuilder();
        var index = 0;
        var rawvalues = new List<object>();
        var pd = PocoData.ForObject(poco, primaryKeyName, PocoDataFactory);
        string versionName = null;
        object versionValue = null;

        var primaryKeyValuePairs = GetPrimaryKeyValues(primaryKeyName, primaryKeyValue);

        var columns = new List<string>();
        foreach (var change in changes)
        {
            columns.Add(change.ColumnName);
        }

        // .... the rest remains the same ...

Makes sense?

DeleteWhere<T>() doesn't support IN

Here's the scenario:

public class MyPoco
{
    public int ID { get; set; }
    public string Name { get; set; }

    public override int GetHashCode()
    {
        return ID;
    }
    public override bool Equals(object obj)
    {
        return (obj != null && obj is MyPoco) ? this.ID.Equals(obj.ID) : false;
    }
}

And here's what I'm trying to do to perform the equivalent of DELETE MyPocos WHERE ID IN (1, 2, 3):

var list = new []
{
    new MyPoco { ID = 1, Name = "One" },
    new MyPoco { ID = 2, Name = "Two" },
    new MyPoco { ID = 3, Name = "Three" }
}

db.DeleteWhere(x => list.Contains(x));    // exception

db.DeleteWhere(x => list.Select(y => y.ID).Contains(x.ID));    // exception

Mapping an enum with underlying Type doesn't work

When I have a POCO with an enum with the underlying Type "byte" (generated from EF) like this I get an InvalidCastException.

enum Days : byte {Sat, Sun, Mon, Tue, Wed, Thu, Fri};

When I remove "byte" it works as expected.

For me currently not a big issue, but I just wanted to report it.

Automapping creates 'empty' object even if FK value is NULL

Hi,

I have a situation, where two tables are connected with ForeigKey (let's imagine Employee and Department tables, and Employee table has DepartmentId column) but this key is nullable.

In my models I have something like:

public class Employee
{
public int Id { get; set; }
public Department Department { get; set; } // Department is another model
}

Now I'm trying to use DBSession.Fetch<Employee, Department>(sql), but for rows where DepartmentId is null on the DB, I get Employee.Department = new Department() instead of Employee.Department = null.

Is it by design or more kind of bug that could be fixed ?

Assigning a nullable int field to a nullable enum

In a Sql Server 2008R2 database, I have a field defined as (int, null). In my NPoco object, I defined it as:

public BenefitType? BenefitType { get; set; }

When I fetch and the database record constains a null value, it works fine. But when there is an integer value into it, I get the message:

Invalid cast from 'System.Int32' to 'System.Nullable`1[[BenefitType, Benefits, Version=1.6.11.43, Culture=neutral, PublicKeyToken=null]]'.

How should I map this?

Best practice for conditionally building queries (with joins)

Is there a best practice in NPoco for conditionally building a query, especially something more complex with conditional adding inner/outer joins with where filters, etc?

Nested Transaction bug ?

I tested this ;

        Market m = new Market();
        m.Designation = "test";

        using (var db = new Database(ConnectionString, DatabaseType.SqlServer2008))
        {
            db.BeginTransaction();
            db.BeginTransaction();
            db.Insert(m);
            db.CompleteTransaction();
            db.AbortTransaction();
        }

the object was inserted into the Database which was not expected...
It seems a bug to me.

Database.Exists is not optimal

There is DatabaseType.GetExistsSql method but it doesn't used in Database.Exists. Moreover Database.Exists is reading full record from database although it can check existance by executing scalar command (this is what PetaPoco v5 is doing).

public virtual OnSomething methods

OnConnectionOpened, OnConnectionClosing, OnBeginTransaction and others should be PROTECTED because only useful for overriding and not for consumer calls.

OneToMany Problem

I think there is an issue with FetchOneToMany <A,B>

If you have
class A
{
public int Id { get; set; }
public IList Messages { get; set; }
}
class B
{
public int AId { get; set; }
public string Text { get; set; }
}

// INNER JOIN
var sql = Sql.Builder.Append("SELECT a., b. FROM A INNER JOIN B ON a.Id = b.AId");
var records = db.FetchOneToMany<A, B>(x => x.Id ,sql);

this will return only A records with matching B records, the expected result in ORM world would be ALL A records with an empty or null IList for A records without matching B table records.

I tried to use LEFT JOIN but than all A records had at least 1 B table record in IList, thats because not matching rows in B table where replaced with NULL values .

There seems to be no way to return all A table records with the IList populated with only matching B table records. Seems NPoco works in a T-SQL manner but it would be better to work in a ORM manner!

Null reference exception when using FetchOneToMany

I am getting an exception when running a OneToMany query where there are no 'many's'.

For example, I have the 2 following pocos:

[TableName("Products")]
[PrimaryKey("IDProduct")]
public partial class Product
{        
    public int IDProduct { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }

    [Ignore]
    public List<ProductCategory> ProductCategories { get; set; }
}

[TableName("ProductCategories")]
[PrimaryKey("IDProductCategory")]
public partial class ProductCategory
{        
    public int IDProductCategory { get; set; }
    public int IDProduct {get; set;}
}

and here is the query I am running:

var sql = @"select Products.*, ProductCategories.* 
              from Products left join ProductCategories on Products.IDProduct =     ProductCategories.IDProduct where Products.IDProduct=@0";

product = db.FetchOneToMany<Product, ProductCategory>(
     p => p.IDProduct, 
     pc => pc.IDProductCategory, 
     sql, id)
     .FirstOrDefault();

The manyKey lambda passed in is causing the problem... when there are no ProductCategories in the database, it gets a null reference exception because null is passed in for 'pc' into the lambda. When there are ProductCategories in the database, then the query works great.

Is this a bug or am I missing something?

Trigger Problem?

Hi

I am having a issue and now I believe it might be a issue with Npoco/ petaPoco as I tried nhibernate and did not get this issue.

Can you please look into this and see if it is something I did wrong or if this in fact is a limitation of Npoco

http://stackoverflow.com/questions/13370931/cannot-insert-data-through-petapoco-npoco-due-to-dml-trigger

Converting property enum value fails if underlying type is not int

Depending on the preferred strategy this can be solved by creating new DatabaseType and overloading method MapParameterValue

OR

change Database.cs:406 (method AddParam)

if (t.IsEnum)       // PostgreSQL .NET driver wont cast enum to int
{
    p.Value = (int)value;
}

if (t.IsEnum)       // PostgreSQL .NET driver wont cast enum to int
{
    var enumUnderlyingType = Enum.GetUnderlyingType(t);
    p.Value = Convert.ChangeType(value, enumUnderlyingType);
}

Unable to cast object of type 'Glimpse.Ado.AlternateType.GlimpseDbConnection' to type 'System.Data.SqlClient.SqlConnection'.

Unable to cast object of type 'Glimpse.Ado.AlternateType.GlimpseDbConnection' to type 'System.Data.SqlClient.SqlConnection'.

[InvalidCastException: Unable to cast object of type 'Glimpse.Ado.AlternateType.GlimpseDbConnection' to type 'System.Data.SqlClient.SqlConnection'.]
   NPoco.SqlBulkCopyHelper.BulkInsert(IDatabase db, IEnumerable`1 list) +187
   NPoco.DatabaseTypes.SqlServerDatabaseType.InsertBulk(IDatabase db, IEnumerable`1 pocos) +80
   NPoco.Database.InsertBulk(IEnumerable`1 pocos) +167

Composite Primary Key

How can I define a composite primary key where only one of the PK fields is autoincrement?

Database(IDbConnection connection) constructor

Constructor is using connection.GetType.ToString() (returns full type name with namespace) but should use connection.GetType().Name because DatabaseType.Resolve searches with string.StartsWith method:

public Database(IDbConnection connection)
    : this(connection, DatabaseType.Resolve(connection.GetType().Name, null))

PagingHelper filename

PagerHelper.cs should be renamed to PagingHelper.cs as class name. Or vice-versa.

getting the primary key of a poco

Is there a way to get the primary key values of a poco (including composite) to use it afterwards for SingleOrDefaultById()?

TransactionScope with NPoco

Hi,

In my project I have mixed data access with NPoco, and plain ADO (I'm trying to migrate to NPoco).
I have set of db operations that must be executed in transaction.
These cover both npoco, and ado actions. I've wrapped all of this in TrasactionScope, but what I was struck with is promoting this to DTC.
As we're working on SQLServer 2008 I would assume, that this operations shouldn't be promoted to DTC (which I don't want to be required for our end users).

Do you have any thoughs about this ?
Have you considered TransactionScope somewhere in NPoco ?

Thanks,
Marcin

Handle System.ComponentModel.DataAnnotations.Schema.NotMappedAttribute same as NPoco.IgnoreAttribute

I am trying to keep my domain-level POCOs persistence agnostic. Unfortunately it's not possible because I have some pseudo-navigation properties (e.g. Author.Books and Book.Author) which must be ignored by the NPoco mapper. So I am forced to add an NPoco reference to my domain POCOs project.

It would be great if we could decorate properties that must be ignored by the mapper with NotMappedAttribute from the System.ComponentModel.DataAnnotations.Schema namespace instead of NPoco's IgnoreAttribute.

I realize this requires .NET 4.5 though but hopefully you can add this enhancement in future NPoco versions.

Proposed change to UpdateContext

I am working on a project where I need to save a history of changes to entities of certain types in a separate table. Each historic change record should contain a list of changes made to individual fields, all concatenated together into a string.

In order to make it happen I want to override the OnUpdating method in my custom class derived from Database and build up a string of changes. However, the current UpdateContext code only stores a list of column names that are about to be updated, not their new values. Therefore I propose the following changes:

1. Move the Change class outside of Snapshotter.
2. Modify the Changes property of UpdateContext to be a list of Changes, not strings, like so:

public class UpdateContext
{
public UpdateContext(object poco, string tableName, string primaryKeyName,
object primaryKeyValue, IEnumerable columnsToUpdate)
{
Poco = poco;
TableName = tableName;
PrimaryKeyName = primaryKeyName;
PrimaryKeyValue = primaryKeyValue;
ColumnsToUpdate = columnsToUpdate;
}

public object Poco { get; private set; }
public string TableName { get; private set; }
public string PrimaryKeyName { get; private set; }
public object PrimaryKeyValue { get; private set; }
public IEnumerable<Change> ColumnsToUpdate { get; private set; }

}

Makes sense?

[dev2.0] Transactions

• Is there really need of PUBLIC Begin/Abort/CompleteTransaction methods if we can use convenient Database.GetTransaction which gives convenient transaction wrapper for using/dispose pattern? Also PUBLIC BaseTransaction property which is used only for wrapper.
• SetTransaction method can be setter property if needed at all.
• Less public methods and properties - easier API.

Possible Transaction Bug?

Hi

I ran into problem a few weeks back and I think it is a bug. What happened is I had to commit some data after X amount of records. I wrapped each of these groups of records in a transaction by doing begin.Transaction and commit(or rollback if something failed).

What though was in my code that there was one case where I would commit transaction A that contained X inserts. I then would make transaction B but due to business logic nothing would get inserted and because how I was doing my checks a 3rd transaction(C) was made and it got filled.

My thinking was when I generated Transaction C and assigned it the same code that transaction B would be killed.

However what seems to be happening is when I did a commit it would either(I can not tell) not commit both transactions or it is commit transaction B and transaction C gets lot what of course left me with lost records.

Not meaningful exception if model doesn't have setter

Hi,

I've just spend some figuring out why, something that worked in NPoco 1.0, throws ArgumentNullException on NPoco 2.0.
The reason was that I was using old fluent configuration method (with a static call), and not DatabaseFactory (generally DatabaseFactory and configuration could be better documented),
therefore NPoco took conventional mapping, with all the columns, and not only the ones I'm interested in.
And there were readonly properties too, which didn't have setters.
I've dug up to PocoData.cs - GetFactory(...) method, which has line:

il.Emit(OpCodes.Callvirt, pc.PropertyInfo.GetSetMethod(true));

So GetSetMethod returned null, and that resulted in ArgumentNullException.
It's very hard to understand what happend for the end user, as ArgumentNullException from internals of NPoco doesn't tell much.
I would vote for checking if given method exists, and throw some better suited exception with clear message to the user (and I assume it's not that only one place, that we could get this problem).

I you would like to see this 'functionality' I could try and write that change and publish pull request over the weekend I think.

Any plans for Code First database generation/migration functionality?

Adam, do you have any plans to add Code First generation/migration of database schema based on POCO's and fluent mappings in the near future?

Too many database connections open

We're due to put our first website live soon using NPoco but today ran into a problem that hopefully you'll be able to help with.

The issue is that NPoco opens and keeps open a database connection - I'm guessing each time I create a new database object. I have set up the code to share the same database connection, however, this is only set up for each request. There might be a requests coming in through MVC and WebAPI for the same page load.

We're using MySQL as the DB and I think it is configured with a 100 connection limit - with each connection "sleeping" for eight minutes it doesn't take long for the database to run out of connections. Even if this was reduced to say 30 seconds, in a live environment it would still run into problems once the bots get going.

Can I configure NPoco to open and close connections as needed or is there an alternative solution I can implement?

Thanks

Chris

Tests

• There is lines in NPoco.Tests/App.config:

Valid Settings:
*     SQLite In-Memory = 2
*     SQL Local DB = 2

Should be:

Valid Settings:
*     SQLite In-Memory = 1
*     SQL Local DB = 2

• Why InMemoryDatabase class which is creating SQLiteConnection, sets provider name to PostgreSQL "Npgsql2"?
• InMemoryDatabase broken because of #36.
• InMemoryDatabase doesn't creating tables so tests are failing. There should be RecreateDataBase() in InMemoryDatabase constructor.
• InMemoryDatabase SQL for creating test tables is not conforms with test classes: for example, class UserDecorated have IsMale property with "is_male" column mapping but InMemoryDatabase users table doesn't create that column.
• Looks like I can't run tests without SQL Server Express installed... sad. Or I'm doing something wrong?

Select<TKey>(Expression<Func<T, TKey>> fields) method question

Hi,

This is more of a question than an issue report:

Method Select<TKey>(Expression<Func<T, TKey>> fields) returns SqlExpression<T> but shouldn't it return SqlExpression<TKey> instead?

I.e. if I am doing a projection like .Select(x => new { ID = x.ID, Name = x.Name }) then the result should be an expression of the anonymous type TKey, right? At least that's how it works in Linq.

Can you comment on that please?

P.S. Just as a background: I am working on a common repository interface that can support both NPoco- and EF-based concrete repositories, and this is one of the requirements.

Named parameters

Hi,

Lately I'm trying to use NPoco to execute stored procedures. Biggest problem was to pass named parameters. I was able to do that with few additions and conditions, but I don't like this code very much and it will be hard for me to keep it up to date with changes on main branch.

Do you think this functionality could find it's place in NPoco codebase ?
If so, I could try and figure out better solution for this.

Regards,
Marcin

InsertBulk throws ArgumentNullException

The InsertBulk method throws an ArgumentNull exception. It appears to be thrown when initializing the SqlBulkLoader (the 'connection' parameter is null). This is occurring with the latest NuGet package.

System.ArgumentNullException: Value cannot be null. Parameter name: connection

the code to reproduce is as basic as it comes:

using(var db = new MyNPocoDbObject("<connection string>", "System.Data.SqlClient"))
{
    db.InsertBulk(myPocoList);
}

UPDATE:
If I change the code to the following it works just fine:

using(var db = new MyNPocoDbObject("<connection string>", "System.Data.SqlClient"))
{
    db.OpenSharedConnection();
    db.InsertBulk(myPocoList);
    db.CloseSharedConnection();
}

I actually have a static GetInstance() method which resolves my connection string based on the current environment and then returns the database object using the above constructor, but I just demonstrated with the constructor for simplicity. All other regular operations work just fine, but I get the exception on the InsertBulk.

Database.ForceDateTimesToUtc obsolete

I don't see any using of Database.ForceDateTimesToUtc property. I think this property and Database.DefaultForceDateTimesToUtc should be removed.

Left joined nullable fields causing conversion issue

I use Entity Framework mappings with NPoco and I have a view using a left join. The numeric fields on the joined table are represented as nullable which causes a problem in NPoco when converting the data.

A typical error looks like this:

Invalid cast from 'System.UInt32' to 'System.Nullable`1[[System.Int64]]

My solution is a SafeConvert method which gets the underlying type and uses that to convert the data instead.

On line 448 of PocoData.cs I changed:

converter = src => Convert.ChangeType(src, dstType, null);

to:

converter = src => SafeConvert(dstType, src);

and the method looks like this:

private static object SafeConvert(Type dstType, object src)
{
    var origType = Nullable.GetUnderlyingType(dstType) ?? dstType;
    return src == null ? null : Convert.ChangeType(src, origType, null);
}

I haven't tested this to destruction yet but I'll let you know if I run into any problems while developing the rest of my application.

If this fix could be added to the next build that would be appreciated.

Thanks

Chris

Attach an existing connection

First, Thank you very much for this nice project. I found it after using a similar project from Rob Cornery. This is simple and very efficient.

Recently, I found a small problem. If I create a connection and start a transaction from this connection and then create an instance of Database with the connection, I'm not able to make query with the Database connection because my transaction cannot be linked to the object Command that you create internally.

Have you some workarounds to this situation?

Tracing\Debugging

Is there any way of displaying the sql that will run with the parameters populated?