collaboratingplatypus / petapoco Goto Github PK

both methods are returning more than one result. The way I work around this is to add "TOP 1" to sql string. Is this the way it's supposed to work?

Line 734: public T SingleOrDefault(string sql, params object[] args)
Line 735: {
Line 736: return Query(sql, args).SingleOrDefault();
Line 737: }
Line 738: public T First(string sql, params object[] args)

Source File: c:\Users\jxd\Documents\My Dropbox\Projects\VS2010\AeroTraq2\AeroTraq\Models\PetaPoco.cs Line: 736

Stack Trace:

[InvalidOperationException: Sequence contains more than one element]
System.Linq.Enumerable.SingleOrDefault(IEnumerable1 source) +4178510 PetaPoco.Database.SingleOrDefault(String sql, Object[] args) in c:\Users\jxd\Documents\My Dropbox\Projects\VS2010\AeroTraq2\AeroTraq\Models\PetaPoco.cs:736 AeroTraq.Controllers.AdminController.Details(String id) in c:\Users\jxd\Documents\My Dropbox\Projects\VS2010\AeroTraq2\AeroTraq\Controllers\AdminController.cs:41 lambda_method(Closure , ControllerBase , Object[] ) +108 System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters) +17 System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters) +208
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters) +27 System.Web.Mvc.<>c__DisplayClass15.<InvokeActionMethodWithFilters>b__12() +55 System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func1 continuation) +263
System.Web.Mvc.<>c__DisplayClass17.b__14() +19
System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodWithFilters(ControllerContext controllerContext, IList1 filters, ActionDescriptor actionDescriptor, IDictionary2 parameters) +191
System.Web.Mvc.ControllerActionInvoker.InvokeAction(ControllerContext controllerContext, String actionName) +343
System.Web.Mvc.Controller.ExecuteCore() +116
System.Web.Mvc.ControllerBase.Execute(RequestContext requestContext) +97
System.Web.Mvc.ControllerBase.System.Web.Mvc.IController.Execute(RequestContext requestContext) +10
System.Web.Mvc.<>c__DisplayClassb.b__5() +37
System.Web.Mvc.Async.<>c__DisplayClass1.b__0() +21
System.Web.Mvc.Async.<>c__DisplayClass81.<BeginSynchronous>b__7(IAsyncResult _) +12 System.Web.Mvc.Async.WrappedAsyncResult1.End() +62
System.Web.Mvc.<>c__DisplayClasse.b__d() +50
System.Web.Mvc.SecurityUtil.b__0(Action f) +7
System.Web.Mvc.SecurityUtil.ProcessInApplicationTrust(Action action) +22
System.Web.Mvc.MvcHandler.EndProcessRequest(IAsyncResult asyncResult) +60
System.Web.Mvc.MvcHandler.System.Web.IHttpAsyncHandler.EndProcessRequest(IAsyncResult result) +9
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +8862381
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +184

The command timeout is always set to 30 seconds and there is no way to override this nor does it take the connection timeout into consideration Need to explicity set the cmd.CommandTimeout on the command object in the CreateCommand method with eiher a property or the connection timeout. This would allow for long running queries across a large database. I noticed when trying to delete 25,000 records it takes about 45 seconds to run but the petapoco timeout at 30 seconds because of the command object.

Skip and Take inherently has much more flexibility when it comes to extract a specific set of records. For a major version upgrade (5.x) I suggest page and page-size is replaced with skip and take. It is a minor code change but adds flexibility where you do not want data by specific pages size. For instance, skip 13 records and return 8, which is a common need for mobile platforms extracting small sets of data per call.

Page and PageSize can be added back in as extension methods or an alternative method name. Internally it would utilize skip and take.

I think it would be usefull to have Exist method which only test for existance at least 1 record in database by where clause.
Here is my quick variant:

/// <summary>
/// Determine if object with specified id is exists within databse.
/// </summary>
/// <typeparam name="T">Type of the entity.</typeparam>
/// <param name="id">Id.</param>
/// <returns>True if object exist in database.</returns>
/// <exception cref="InvalidOperationException">For use Exist method the entity type should be marked with TableName attribute.</exception>
/// <exception cref="InvalidOperationException">For use Exist method the entity type should be marked with PrimaryKey attribute.</exception>
public virtual bool Exist<T> (object id)
{
    var pd = Database.PocoData.ForType (typeof (T));

    if (string.IsNullOrEmpty (pd.TableName))
        throw new InvalidOperationException ("For use Exist method the entity type should be marked with TableName attribute.");

    if (string.IsNullOrEmpty (pd.PrimaryKey))
        throw new InvalidOperationException ("For use Exist method the entity type should be marked with PrimaryKey attribute.");

    return this.GetDatabase ().SingleOrDefault<object>
        (Sql.Builder
            .Select (pd.PrimaryKey)
            .From (pd.TableName)
            .Where (pd.PrimaryKey + " = @0", id)
        ) != null;
}

currently call goes through Query which queries the database and base on my understanding scans the whole table even if all you need is the first result,

SELECT [id], [title], [draft], [date_created], [date_edited], [content], [state], [col w space], [nullreal] FROM [petapoco] WHERE id=@0

above is the query generated base on SingleOrDefault_Single test.

wouldn't it make sense to add a TOP/LIMIT Clause to the end of the query in these cases?

Trying to insert byte[] value to a SQL Server VarBinary(MAX) column fails with the following exception:

"implicit conversion from data type nvarchar to varbinary(max) is not allowed"

Tried setting parameter.DbType to DbType.Binary and size to -1, the size of the byte array, hard coded 4000 but no luck... .always the same error.

Is it possible to get this function included in "static string Cleanup(string str)? It could be activated through a configuration option or something. It makes all models/properties follow .NET naming guidelines.

    static string ToPascalCase(string value)
    {
        string result = "" + char.ToUpper(value[0]);
        for (int i = 1; i < value.Length; i++)
        {
            if (value[i] == '_')
            {
                while (i < value.Length && value[i] == '_')
                    ++i;

                if (i < value.Length)
                    result += char.ToUpper(value[i]);
            }
            else
                result += value[i];
        }

        return result;
    }

Here is a code:

...
string city = ...
IEnumerable<int> countries = ...
...

var sql = Sql.Builder
    .Select ("t.Id")
    .From ("dbo.TourAgents as t with (nolock)")
    .InnerJoin ("Users.dbo.Users as u with (nolock)").On ("u.Id = t.Id")
    .InnerJoin ("Users.dbo.UserStates as s with (nolock)").On ("s.Id = u.StateId");

if (!city.IsNullOrWhiteSpace ())
    sql = sql.Where ("t.LocationRegionName like @0 or t.LocationCity like @0", "%" + city.Trim () + "%");

if (countries != null && countries.Any ())
{
    sql = sql
        .InnerJoin ("dbo.TourAgentSpecCountries as tasc with (nolock)").On ("tasc.UserId = t.Id")
        .Where ("tasc.CountryId in (@0)", countries);
}

This will result in this SQL:

SELECT t.Id
FROM dbo.TourAgents as t with (nolock)
INNER JOIN Users.dbo.Users as u with (nolock)
ON u.Id = t.Id
INNER JOIN Users.dbo.UserStates as s with (nolock)
ON s.Id = u.StateId
WHERE t.LocationRegionName like @0 or t.LocationCity like @1
INNER JOIN dbo.TourAgentSpecCountries as tasc with (nolock)
ON tasc.UserId = t.Id
WHERE tasc.CountryId in (@2)

I'm seeing p.size=4000 at line 391, i dont think guid parameters need that amount of size, could it be a bug?

I would also reccomend switch case instead of if/else if statements, not only referring to my knoiwledge but also to a discussion going on stackoverflow (http://stackoverflow.com/questions/767821/is-else-if-faster-than-switch-case/767849#767849)

Peace,

If you create a nullable unsigned int field in a MySql table and try to select records from it using PetaPoco, you get the exception "Invalid cast from 'System.UInt32' to 'System.Nullable`1[[System.Int32, mscorlib..". This is using the objects created by the T4 template. The only way around it is to change the field in the table to a signed int or to edit the generated object to a nullable unsigned int.

I use SQL Server's schema objects to help organize tables in my databases. Sometimes these tables have identical names causing a couple of problems with PetaPoco.

1. The T4 templates produce mulitple "table" classes with the same name.
2. The T4 templates interlace the columns from both tables into one table.
3. While we are at it, add the ability to add prefix and suffix to the table class name.

I have workarounds that fix these problems. I submit them for inclusion in PetaPoco if you think others can benefit from them.

In PetaPoco.Core.ttinclude

1. Add 4 fields at the top of the file these will be initialized by a new method GetConfigSettings() which reads data from the .config file

    // Change - Add these fields to top of class
    public string TablePrefix = "";                 //Read from app.config used by T4 generator template to add prefix to PetaPoco Table classes
    public string TableSuffix = "";                 //Read from app.config used by T4 generator template to add prefix to PetaPoco Table classes
    public static string PrimarySchema = "dbo";     //Read from app.config used by T4 core template differentiate tables with the same name from different schemas
    public static bool IncludeViews = false;        //Read from app.config used by T4 core template include views as PetaPoco Table classes

2. Add fields to store schema data read from the database

    public class Table
    {
        public List<Column> Columns;
        public string Name;
        public string CleanName;
        public string ClassName;

        // Change - Add the following Fields
        public string Schema;   //Used in the ReadSchema method - Allows differentiation of tables with the same name but diferent schema
        public string Catalog;  //Because it is available when initialization occurs. Could be useful in future
        public string Type;     //Because it is available when initialization occurs. Could be useful in future

        public Column PK
        {
            get
            {
                return this.Columns.SingleOrDefault(x => x.IsPK);
            }
        }
    }

3. Add AppSettings section to .config file

    <appSettings>
        <add key="TablePrefix" value=""/>
        <add key="TableSuffix" value="Table"/>
        <add key="PrimarySchema" value="dbo"/>
        <add key="IncludeViews" value="true"/>
    </appSettings>

4. Create GetAppConfigSettings() method

    void GetAppConfigSettings()
    {
        try
        {
            ExeConfigurationFileMap configFile = new ExeConfigurationFileMap();
            configFile.ExeConfigFilename = GetConfigPath();

            if (string.IsNullOrEmpty(configFile.ExeConfigFilename))
                throw new ArgumentNullException("The project does not contain App.config or Web.config file.");

            var config = System.Configuration.ConfigurationManager.OpenMappedExeConfiguration(configFile, ConfigurationUserLevel.None);

            TablePrefix = config.AppSettings.Settings["TablePrefix"].Value;
            TableSuffix = config.AppSettings.Settings["TableSuffix"].Value;
            PrimarySchema = config.AppSettings.Settings["PrimarySchema"].Value;
            IncludeViews = Convert.ToBoolean(config.AppSettings.Settings["IncludeViews"].Value);
        }
        catch
        {
            TablePrefix = string.Empty;
            TableSuffix = string.Empty;
            PrimarySchema = "dbo";
            IncludeViews = false;
        }
    }

5. Call GetConfigSettings() from LoadTables()

    Tables LoadTables()
    {
        InitConnectionString();
        GetConfigSettings(); // Change - Add this call in the List<Table> LoadTables() method

        // ...
    }

6. Initialize new fields in Table class and rename Table Class to include schema for non-primary schemas

    public override List<Table> ReadSchema(DbConnection connection, DbProviderFactory factory)
    {
        // ...

        using (cmd)
        {
            using (var rdr=cmd.ExecuteReader())
            {
                while (rdr.Read())
                {
                    Table tbl=new Table();

                    // ...

                    // Initialize additional table fields
                    tbl.Catalog = rdr["TABLE_CATALOG"].ToString();
                    tbl.Schema = rdr["TABLE_SCHEMA"].ToString();
                    tbl.Type = rdr["TABLE_TYPE"].ToString();
                    if (tbl.Schema != PrimarySchema)  // PrimarySchema from .config file
                    {
                        tbl.ClassName = tbl.Schema + "_" + tbl.ClassName; // Add Schema to differentiate tables not in the PrimarySchema
                    }

                    // ...
                }
            }
        }
        // ...

        return result;
    }

7. Change COLUMN_SQL, adding a schema parameter

    //  const string COLUMN_SQL=@"SELECT 
    //          TABLE_CATALOG AS [Database],
    //          TABLE_SCHEMA AS Owner, 
    //          TABLE_NAME AS TableName, 
    //          COLUMN_NAME AS ColumnName, 
    //          ORDINAL_POSITION AS OrdinalPosition, 
    //          COLUMN_DEFAULT AS DefaultSetting, 
    //          IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType, 
    //          CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
    //          DATETIME_PRECISION AS DatePrecision,
    //          COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
    //          COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
    //      FROM  INFORMATION_SCHEMA.COLUMNS
    //      WHERE TABLE_NAME=@tableName
    //      ORDER BY OrdinalPosition ASC";

    const string COLUMN_SQL=@"SELECT 
        TABLE_CATALOG AS [Database],
        TABLE_SCHEMA AS Owner, 
        TABLE_NAME AS TableName, 
        COLUMN_NAME AS ColumnName, 
        ORDINAL_POSITION AS OrdinalPosition, 
        COLUMN_DEFAULT AS DefaultSetting, 
        IS_NULLABLE AS IsNullable, DATA_TYPE AS DataType, 
        CHARACTER_MAXIMUM_LENGTH AS MaxLength, 
        DATETIME_PRECISION AS DatePrecision,
        COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') AS IsIdentity,
        COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsComputed') as IsComputed
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME=@tableName AND TABLE_SCHEMA = @schemaName
    ORDER BY OrdinalPosition ASC";

8. Change LoadColumns() method passing schema parameter

    List<Column> LoadColumns(Table tbl)
    {
        using (var cmd=_factory.CreateCommand())
        {
            cmd.Connection=_connection;
            cmd.CommandText=COLUMN_SQL;

            var p = cmd.CreateParameter();
            p.ParameterName = "@tableName";
            p.Value=tbl.Name;
            cmd.Parameters.Add(p);

            var p2 = cmd.CreateParameter();
            p2.ParameterName = "@schemaName";
            p2.Value=tbl.Schema;
            cmd.Parameters.Add(p2);

            // ...
        }
    }

PetaPoco.Generator.ttinclude

Add Prefix and Suffix to Table

    foreach(Table tbl in from t in tables where !t.Ignore select t)
    {
        //TablePrefix & TableSuffix are read from .config file
        string tableName = TablePrefix + tbl.ClassName + TableSuffix;

Then use this in the template code

    public partial class <#=tableName#> <# if (GenerateOperations) { #>: <#=RepoName#>.Record<<#=tableName#>><# } #> 

If I try to get a paged result with a "SELECT *" query in oracle invalid sql is generated.

The first fetch works, the second doesn't. The SQL generated for the second fetch is:
SELECT *
FROM (SELECT ROW_NUMBER () OVER (ORDER BY acct_id) peta_rn, *
FROM account
WHERE date_closed IS NOT NULL) peta_paged
WHERE peta_rn > 1
0 AND peta_rn<=20

This fails with a sql error of "[Error] Execution (15: 63): ORA-00936: missing expression". It would work if ACCOUNT was aliased with "A", which I could do in the query that I feed to Fetch, however it's not obvious that is required. It would be nice if the method at least threw an exception indicating that an alias is required when using paging. It would be better if the sql generation somehow handled it for me.

    [Test]
    public void PagedTest()
    {
        using(var kdb = new KbwDatabase())
        {
            try
            {
                //Works
                var results = kdb.Fetch<Account>(1, 20, "WHERE DATE_CLOSED IS NOT NULL ORDER BY ACCT_ID");
                Expect(results.Count, Is.EqualTo(20));

                //Does Not Work
                results = kdb.Fetch<Account>(1, 20, "SELECT * FROM ACCOUNT WHERE DATE_CLOSED IS NOT NULL ORDER BY ACCT_ID");
                Expect(results.Count, Is.EqualTo(20));
            }
            finally
            {
                Debug.WriteLine(kdb.LastCommand);
            }
            return;
        }
    }

    [TableName("ACCOUNT")]
    class Account
    {
        public string AcctId { get; set; }
        public string HouseCust { get; set; }
        public DateTime? DateOpened { get; set; }
        public DateTime? DateClosed { get; set; }
        [Column(Name="ACCT_TEXT1")]
        public string AccountText1 { get; set; }
    }

Oracle T4 correct work. bugs:
CommandText =
const string TABLE_SQL=@"select TABLE_NAME from USER_TABLES";
and read TABLE_SCHEMA, TABLE_TYPE.

cmd.GetType().GetProperty("BindByName").SetValue(cmd, true, null);
OracleCommand don't have BindByName Property -> Exception

By default Poco uses "@n" syntax to define user-passed variables in SQL.
The thing is that the same syntax is used in MSSQL "for xml path" select.

For example this is correct MSSQL syntax:

select
    t.Id as '@id'
from
    dbo.MyTable as t
where
    t.Name = 'Michael'
for xml path('Item'), root ('Root'), type

Which should result in XML like this:

<Root>
    <Item id="1" />
    <Item id="2" />
</Root>

But if I try to execute this SQL with Poco:

var Sql = new Sql
(@"
    select
        t.Id as '@id'
    from
        dbo.MyTable as t
    where
        t.Name = @0
    for xml path('Item'), root ('Root'), type
",
"Michael");

I've got error:

Parameter '@id' specified but none of the passed arguments have a property with this name

It's very common for SQL queries to have a "where in (@0)" conditions. PetaPoco works fine with it but there are some problems.

var ids = new[] {};
return db.Query<MyEntity> (Sql.Builder.Where ("Id in (@0)", ids));

This will generate invalid SQL. But I understand that this is not a trivial task to solve (because it could be a very complex expressions with multiple variables passed to Where method and get rid of the part that relly on passed empty sequence parameter is require parsing the whole SQL). Another problem is that PetaPoco generates multiple input parameters for each of the item in the enumeration. So if I have like 1000 ids it's gonna be hell of a SQL with 1000 parameters. Since the most common "where in" clause is filtering by numerical enumerations I have a suggestion to include some additional WhereIn methods that will take care of big enumeration parameters and also properly work with empty sequence problem:

/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <typeparam name="T">Type of the id.</typeparam>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereIn<T> (this Sql sqlObj, string columnName, IEnumerable<T> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " in (@0)", ids);
}


/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereIn (this Sql sqlObj, string columnName, IEnumerable<int> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " in (" + string.Join (",", ids.Select (x => x.ToString ()).ToArray ()) + ")");
}


/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereIn (this Sql sqlObj, string columnName, IEnumerable<long> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " in (" + string.Join (",", ids.Select (x => x.ToString ()).ToArray ()) + ")");
}

/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <typeparam name="T">Type of the id.</typeparam>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereNotIn<T> (this Sql sqlObj, string columnName, IEnumerable<T> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " not in (@0)", ids);
}


/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereNotIn (this Sql sqlObj, string columnName, IEnumerable<int> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " not in (" + string.Join (",", ids.Select (x => x.ToString ()).ToArray ()) + ")");
}


/// <summary>
/// Appends a "<paramref name="columnName"/> in (<paramref name="ids"/>)" sql (if <paramref name="ids"/> is not null or empty).
/// </summary>
/// <param name="sqlObj">Sql builder object.</param>
/// <param name="columnName">Column name (with alias).</param>
/// <param name="ids">List of ids (can be null).</param>
public static Sql WhereNotIn (this Sql sqlObj, string columnName, IEnumerable<long> ids)
{
    if (ids == null || !ids.Any ())
        return sqlObj;

    return sqlObj.Where (columnName + " not in (" + string.Join (",", ids.Select (x => x.ToString ()).ToArray ()) + ")");
}

I use code generation to create FluentValidation (http://fluentvalidation.codeplex.com/) validator classes.

I have made a few modifications to PetaPoco.cs and the T4 templates to support the code gen of these classes

If you agree i would love to see this included in the product.

In PetaPoco.cs

    // Property Attribute used to pass metadata to Validator class code generator
    // Marks property as nullable
    [AttributeUsage(AttributeTargets.Property)]
    public class NullableAttribute : Attribute
    {
    }

    // Property Attribute used to pass metadata to Validator class code generator
    // Marks string length
    [AttributeUsage(AttributeTargets.Property)]
    public class MaxLengthAttribute : Attribute
    {
        public MaxLengthAttribute() { }
        public MaxLengthAttribute(int maxLength) { MaxLength = maxLength; }
        public int MaxLength { get; set; }
    }

In PetaPoco.Core.ttinclude
Add a public field ("MaxLength") to the Column Class

public class Column
{
    public string Name;
    public string PropertyName;
    public string PropertyType;
    public bool IsPK;
    public bool IsNullable;

    // Change - Add the following Field
    public int MaxLength;   //Used by [MaxLength(MaxLength)] attribute and ultimately by EntityValidator Class
}

In PetaPoco.Generator.ttinclude
In the column output section add the following code. It adds padded attributes to the column property declarations

foreach(Column col in from c in tbl.Columns where !c.Ignore select c)
{
    // Column bindings
    string nullable = "          ";
    if(col.IsNullable==true)
        nullable = "[Nullable]";

    string maxLength = "";
    if(col.PropertyType=="string")
        maxLength = "[MaxLength(" + col.MaxLength.ToString() + ")]";
    maxLength = maxLength.PadRight(17);

    string propertyName = col.PropertyName;

    string propertyType = (col.PropertyType + CheckNullable(col));
    propertyType = propertyType.PadRight(12);
}

I changed the template code to

<# if (col.Name!=col.PropertyName) { #>
[Column("<#=col.Name#>")] <#=nullable#> <#=maxLength#> public <#=propertyType#> <#=propertyName#> { get; set; }
<# } else { #>
[Column] <#=nullable#> <#=maxLength#> public <#=propertyType#> <#=propertyName#> { get; set; }
<# } #>

Have you considered or tested support for SQLite and Firebird databases? I think that these are common among .NET developers, and have active developed and supported providers.

Queries with DISTINCT generate invalid SQL when using Page method to fetch data.

It would be nice to have some method (maybe not only delete) that will deletes row from the database only by it's primary key. Usefull for the times you have only id but don't have full object.

Here is my version:

/// <summary>
/// Delete object from database by id.
/// </summary>
/// <typeparam name="T">Type of the entity.</typeparam>
/// <param name="id">Id.</param>
/// <exception cref="InvalidOperationException">For use DeleteById method the entity type should be marked with TableName attribute.</exception>
/// <exception cref="InvalidOperationException">For use DeleteById method the entity type should be marked with PrimaryKey attribute.</exception>
public virtual void DeleteById<T> (object id)
{
    var pd = Database.PocoData.ForType (typeof (T));

    if (string.IsNullOrEmpty (pd.TableName))
        throw new InvalidOperationException ("For use Exist method the entity type should be marked with TableName attribute.");

    if (string.IsNullOrEmpty (pd.PrimaryKey))
        throw new InvalidOperationException ("For use Exist method the entity type should be marked with PrimaryKey attribute.");

    this.GetDatabase ().Delete (pd.TableName, pd.PrimaryKey, null, id);
}

Am I correct to assume that "IsNew" and "Save" will only work properly if the database is using an auto generating primary key?

If the the database is using a "natural" primary key like an invoice number that is being created by the program then that property will be populated by the program when the object is created and the IsNew function will return false (because the primary key is populated) even though the record is not already in the database. Therefore, "Save" will not work properly.

Am I understandingt that correctly? If so, are there any plans to have these functions work with "natural" primary keys?

If you have a Messages table with a column called Message the T4 templates creates a Message class with a Message property and therefore won't compile

I have this POCO entity:

[PetaPoco.TableName ("Website.Countries")]
[PetaPoco.PrimaryKey ("Id")]
public class Country
{
    public virtual int Id { get; set; }
    public virtual string Code { get; set; }
    public virtual string Name { get; set; }
}

For this statement:

db.FirstOrDefault<Country> (Sql.Builder.Where ("Name = @0", name));

PetaPoco (3.0.1) generating incorrect SQL:

SELECT [Website.Countries].[Id], [Website.Countries].[Code], [Website.Countries].[Name] FROM [Website.Countries] WHERE (Name = @0)

There should be a way to manually provide table schema name (probably in TableName attribute) since it's vital for databases with non default schemas. Or maybe tweak table name escaping algorythm.

I think it would be nice to have helpers like Sql.Builder.SelectPrimaryKey () and Sql.Builder.WherePrimaryKey (" = @0", 123).

A new .NET 3.5 class ReaderWriterLockSlim allows a better way for working with non thread-safe objects. I think that perfomance of using PetaPoco in heavy load multithread environment (website for example) can be greatly improved by applying ReaderWriterLockSlim instead of full lock because ReaderWriterLockSlim can sepparate read and read/write locks. This way, for example, multiple threads can call metho Database.ForType without waiting for each other.

http://msdn.microsoft.com/en-us/library/system.threading.readerwriterlockslim.aspx

Can we have PocoData to be not internal so we could write some methods depending on it in other libraries too?

I attempted what seems to be a currently unsupported feature

var profile = db.Single("FROM dbo.[User] WHERE externalid = @0", id);

I think this query should work out of box.

added the following to public object Insert/Update(...)

if (_dbType == DBType.Oracle)
cmd.CommandText = cmd.CommandText.Replace("[", "").Replace("]", "");

Currently, when you call GetTransaction() it returns a concrete instance of the Transaction class. which makes it almost impossible to mock. please either introduce an interface or at least make the Complete() method Virtual so it can be mocked.

The MultiPocoQuery method is internal so to use it I have to make it public. One day I'll stop going on about access modifiers :)

I have an int column in Sqlite that maps to a Nullable DayOfWeek Enum, trying to read any rows from the db causes

Invalid cast from 'System.Int32' to 'System.Nullable`1[[System.DayOfWeek]]

is there a workaround I could use or is it a bug that needs to be fixed?

btw, love your ORMish DAL.

VS 2010 Sp1 Windows 7 X64

Error 7 Loading the include file 'PetaPoco.Generator.ttinclude' returned a null or empty string. The transformation will not be run. C:\Projects\crm\Models\Generated\Records.tt 13 4
Error 6 Loading the include file 'PetaPoco.Core.ttinclude' returned a null or empty string. The transformation will not be run. C:\Projects\crm\Models\Generated\Records.tt 1 4
Error 5 Failed to resolve include text for file:C:\Projects\crm\Models\Generated\PetaPoco.Generator.ttinclude C:\Projects\crm\Models\Generated\Records.tt 0 0
Error 4 Failed to resolve include text for file:C:\Projects\crm\Models\Generated\PetaPoco.Core.ttinclude C:\Projects\crm\Models\Generated\Records.tt 0 0

I'm interested in using stackoverflow's MiniProfiler and got it working by modifying two lines, but I wondered if there was a way to do it without changing PetaPoco.cs.

First when creating a command (CreateCommand method), PetaPoco uses _factory if present, otherwise uses connection.CreateCommand. Is there a reason not to use connection.CreateCommand all the time? This is one of the changes I needed to make to get MiniProfiler to work.

The second change was adding this line to OpenSharedConnection:
_sharedConnection = MvcMiniProfiler.Data.ProfiledDbConnection.Get(_sharedConnection as DbConnection);

If PetaPoco had a method for modifying connections it wouldn't have to reference the new assembly. This doesn't seem the best way though... One for Command might be nice to to enable things like global CommandTimeout changes because I don't like the default timeout, but that might be a setting I could add to the database object...

Declaration in PetaPoco.Database:
public static Func<IDbConnection, IDbConnection> ModConnection = null;

And in OpenSharedConnection:
if (ModConnection != null)
_sharedConnection = ModConnection(_sharedConnection);

And in Application_Start:
PetaPoco.Database.ModConnection = delegate(IDbConnection cnn)
{
return MvcMiniProfiler.Data.ProfiledDbConnection.Get(cnn as DbConnection);
};

Since PetaPoco is a single file it might just be better to modify it specifically for this task... Thoughts?

Here's another SQLServer quirk.

In addition to DateTime they include a Time type.

Please add another type to the switch statement in the GetPropertyType() method found in PetaPoco.Core.ttinclude

I know I just hit you with a bunch of change requests. If it helps I can send you my files - let me know how.

    string GetPropertyType(string sqlType)
    {
        string sysType="string";
        switch (sqlType)
        {
                // ...

                // Here's a new one - SQL Server has a Time Type in addition to DateTime
            case "time":
                sysType = "DateTime";
                break;

        }
        return sysType;
    }

PetaPoco looks for public parameterless constructors (ie; the compiler default) but I needed protected parameterless constructors on the objects I'm sending to PetaPoco. So I changed GetFactory() where it looks for the constructor from :

il.Emit(OpCodes.Newobj, typeof(T).GetConstructor(Type.EmptyTypes));

To :

il.Emit(OpCodes.Newobj, typeof(T).GetConstructor( BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic, null, new Type[0], null));

Perhaps this can be added to PetaPoco?
Thanks

I downloaded PP to experiment. I am attempting to us it with the Northwind database. Many of the column names in Northwind have embedded spaces. When I do a query all of the columns that have names with spaces return null. Also, if I attempt to use Automatic Select clauses on a table that has columns with embedded spaces in their names the program throws an exception.

Is there a work around for this or is it not possible to work with column names that have spaces?

I think you should separate the generator code from PetaPoco and place it probably in a separate Nuget package like PetaPoco.Generator.

If you wouldn't feel it's a good thing to do for the project to split these up, can you make it so that PetaPoco doesn't automatically generate POCO files?

It's quite unnerving seeing a tool has specifically called my database and made copies of my entire database schema without my approval. Especially when it even uses the wrong database because PP is doing too much GUESSING.

I would recommend changing

new PetaPoco.Sql()
    .Select("*")

To be invoked like

PetaPoco.Sql.Select...

This would be as simple as creating a static wrapper

public class PetaPoco {

    public Sql Sql { return new Sql() }

}

This really is more of a discussion but GitHub doesn't have discussions like Codeplex

MSSQL 2005
Trying to do something like this:

var ids = new Database ("MyDb").Fetch<long> ("select t.Id from dbo.MyObjects as t");

Result in this:

System.ArgumentNullException: Value cannot be null.
Parameter name: con
   at System.Reflection.Emit.DynamicILGenerator.Emit(OpCode opcode, ConstructorInfo con)
   at PetaPoco.Database.PocoData.GetFactory[T](String key, Boolean ForceDateTimesToUtc, IDataReader r) in PetaPoco.cs:line 1386
   at PetaPoco.Database.<Query>d__7`1.MoveNext() in PetaPoco.cs:line 675
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at PetaPoco.Database.Fetch[T](String sql, Object[] args) in PetaPoco.cs:line 543
   at PetaPoco.Database.Fetch[T](Sql sql) in PetaPoco.cs:line 548
   at MyRepository.Search() in MyRepository.cs:line 58

Loads of stored procs use output parameters especially when using SQL 2000 as there were no table return types before...

In order to write to a SQL Server SqlGeography or SqlGeometry column I have made a couple of changes to the PetoPoco.cs file.

Please include in PetaPoco. Thanks.

    void AddParam(IDbCommand cmd, object item, string ParameterPrefix)
    {
        // ...

        var p = cmd.CreateParameter();
        p.ParameterName = string.Format("{0}{1}", ParameterPrefix, cmd.Parameters.Count);
        if (item == null)
        {
            p.Value = DBNull.Value;
        }
        else
        {
            if (item.GetType().IsEnum)      // PostgreSQL .NET driver wont cast enum to int
            {
                p.Value = (int)item;
            }
            else if (item.GetType() == typeof(Guid))
            {
                p.Value = item.ToString();
                p.DbType = DbType.String;
                p.Size = 4000;
            }
            else if (item.GetType() == typeof(string))
            {
                p.Size = (item as string).Length + 1;
                if (p.Size < 4000)
                    p.Size = 4000;      // Help query plan caching by using common size
                p.Value = item;
            }

            // ...
            //The following changes allows for the writing of SqlServer geography & geometry types
            // 
            else if (item.GetType().Name == "SqlGeography") //SqlGeography is a CLR Type
            {
                var p2 = p as System.Data.SqlClient.SqlParameter;
                p2.UdtTypeName = "geography"; //geography is the equivalent SQL Server Type
                p2.Value = item;    
            }

            else if (item.GetType().Name == "SqlGeometry") //SqlGeometry is a CLR Type
            {
                var p2 = p as System.Data.SqlClient.SqlParameter;
                p2.UdtTypeName = "geometry"; //geometry is the equivalent SQL Server Type
                p2.Value = item;    
            }

            else
            {
                p.Value = item;
            }
        }

        cmd.Parameters.Add(p);
    }

Love PetaPoco, one feature lacking compared to dapper is Multi Mapping. I would love it added in as it is really important for efficiently returning associations. Had a look myself but all of the emit code is over my head.

The two static Update methods in Record, from PetaPoco.Generator.ttinclude, should call repo.Update instead of repo.Delete.

Sometimes I want to use a SQL Server view in PetaPoco.

In order to generate PetaPoco tables encapsulating these views I make a couple of changes to your files. I have added my code changes below. Please use as you see fit.

Please refer to Issue 30 which shows the "reading" of the "IncludeView" value from the .config flag. this allows for the easy switching of this feature (the default is "false")

In PetaPoco.Core.ttinclude

1. Change the SQL string to get primary keys of Schema Bound views

    string GetPK(string table)
    {
        // This code gets the primary key of Schema Bound views in addition to tables

        //string sql=@"SELECT KCU.COLUMN_NAME 
        //  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
        //  JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
        //  ON KCU.CONSTRAINT_NAME=TC.CONSTRAINT_NAME
        //  WHERE TC.CONSTRAINT_TYPE='PRIMARY KEY'
        //  AND KCU.TABLE_NAME=@tableName";

        string sql=@"SELECT c.name AS ColumnName
                FROM sys.indexes AS i 
                INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id 
                INNER JOIN sys.objects AS o ON i.object_id = o.object_id 
                LEFT OUTER JOIN sys.columns AS c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
                WHERE (i.type = 1) AND (o.name = @tableName)";

        // ...
    }

2. Add the following property

    public string TableCommandText
    {
        get
        {
            if (IncludeViews == true)
                return TABLE_SQL_INCLUDE_VIEWS;
            else
                return TABLE_SQL;
        }
    }

    const string TABLE_SQL_INCLUDE_VIEWS =@"SELECT * FROM  INFORMATION_SCHEMA.TABLES";
    const string TABLE_SQL=@"SELECT * FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'";

3. Use the property in the ReadSchema() method

    public override List<Table> ReadSchema(DbConnection connection, DbProviderFactory factory)
    {
        var cmd=_factory.CreateCommand();

        // ...

        //cmd.CommandText=TABLE_SQL;
        cmd.CommandText = TableCommandText; // Change - Allows the inclusion of views

        // ...

    }

There are certain references needed by PetaPoco (for example System.Configuration) which are not added automatically by the NuGet Package. Not the end of the world but it would be nice if they were added automatically.

I use PetaPoco in my repositories and I use autogeneration a lot.
But sometimes I need to Query<> for some custom SQL without PetaPoco's autogeneration (for example, using CTE in MSSQL in which case PetaPoco's autogenerator will make invalid SQL). Turning Database.EnableAutoSelect on and off for each of those queries require to wrap functionality inside try/finnaly (in case of error) since the instance of the PetaPoco database is shared between all repositories that work with the same database.

So it would be nice to have some way to turn off autogeneration for a given query. Maybe with some "DisableAutoGeneration" property in Sql object or something like this.

Example were PetaPoco autogenerator will make invalid SQL:

public class DayOfferTourTopForCountryInfo
{
    public int CountryId { get; set; }
    public int TourId { get; set; }
}

public virtual IEnumerable<DayOfferTourTopForCountryInfo> QueryDayOfferToursTopForCountries ()
{
    return this.GetDatabase ().Query<DayOfferTourTopForCountryInfo>
    (@"
        ;with R as
        (
            select
                t.Id,
                t.CountryId,
                row_number () over (partition by t.CountryId order by t.DayOfferEndDate) as N
            from
                dbo.TourOffers as t
                inner join dbo.Countries as c on (c.Id = t.CountryId)
            where
                t.DayOfferEndDate is not null
        )

        select
            t.Id as CountryId,
            isnull (t.DayOfferTourId, t.LatestTourId) as TourId
        from
            (
                select
                    c.Id,
                    c.IsPopular,
                    r.Id as DayOfferTourId,
                    (
                        select top 1 t.Id
                        from dbo.TourOffers as t
                        where
                            t.State = 0
                            and
                            t.CountryId = c.Id
                        order by t.CreationDate desc
                    ) as LatestTourId
                from
                    dbo.Countries as c
                    left join R as r on (r.CountryId = c.Id)
                where
                    isnull (r.N, 1) = 1
            ) as t
        where
            (t.DayOfferTourId is not null)
            or
            (t.IsPopular = 1 and t.LatestTourId is not null)
        order by
            newid ()
    ");
}

The method SplitSqlForPaging returns false if the SQL statement has no ORDER BY part. Ordering should not be required for paging of the data and the method should simply skip the last parsing section if there is no ordering. Returning false will trigger an exception in BuildPageQueries.

Repeated entity instances?

Code example in blog post about MultiPoco support shows a Many-to-One relationship. Imagine we return 10 articles that are all authored by the same user. But what will the end result be? There will of course be 10 article object instances. But what about their "Author" property? Will it point to the same User object instance or will every article point to a different instance but with the same data?

What about One-to-Many results?

And also: What about One-to-Many relations? In sense of returning articles and their comments? Each row would return article and comment POCOs at the same time. Articles will be repeated over several rows because each will have one comment. How should we handle this situation? Articles have a "List" property.

It's obvious that One-to-One relations are easily done with MultiPoco queries. It's definitely not straight forward to see a scenario with at least one side (or both) being a "Many" relation.

Could you answer my two questions here and hopefully also write a blog post with detailed examples of using MultiPoco support with various relation types.

The description for Nuget still defines PP as for use with non dynamic objects implying a derivation from massive. I would recommend updating this to convey it supports both dynamics like massive and POCO objects.

It must be a good day when you have an issue list for an open source project that the entirety of it consists of saying the package definition is too out of date that it's missing features :)

The latest version of PetaPoco (3.0.2) has some methods which relly on Func<T1, T2, T3, T4, T5, TRet> which is not present in .NET 3.5 - so the source code can not compile.

Temprary workaround is to declare required type in PetaPoco namespace:

namespace PetaPoco
{
    public delegate TResult Func<T1, T2, T3, T4, T5, TResult> (T1 arg1, T2 arg2, T3 arg3, T4 arg4, T5 arg5);
}

I'm sorry if I write feature suggestion as an issue but I don't know of a better place to post a suggestion.

It would be nice for PetaPoco to support multi result-set queries (at least in terms of stored procedures). This is the best way to avoid the N+1 problem. Related properties (as One- or Multi- relations) could as well be automatically resolved either by using some sort of relator lambda expressions or POCO attributes. The latter being favoured so we don't have to repeat our code every time we'd like to auto-relate entities.