henkmollema/dommel

CRUD operations with Dapper made simple.

dommel's Issues

Allow manually entered key property

Right now the sql builders just don't insert the key property

Custom column mapping

One should be able to configure a custom column mapping for an entity property which should be used in every CRUD operation.

Query logging

I would like to get the query built?

v2

• Split classes into files
• Support [Table] attribute
• Support [Column] attribute
• Support [ForeignKey] attribute - #72
• Logging of queries
• Fix joining tables (#29, #35)
• Automatic multi-mapping single item
• Add transaction params - #31
• Paging on Get-queries
• Paging on Select-queries
• Automatic multi-mapping collection
• Multiple primary keys - #54
• Unit tests
• Extend select functionality (e.g. OrderBy #56 )
• More advanced SQL builder class #84
  • Select all entities
  • Select single entity
  • Insert entity
  • Update entity
  • Delete entity
  • Where-clause
  • and/or-clause
• Projections #85

Add GetAll method

Add the GetAll() method which will return all the objects in the table.

insert list

How to insert List

Multiple RDBMS support

Currently, the queries are SQL Server specific. There should be a mechanism which builds queries for a certain RDBMS based on the current connection.

Create implementations of the ISqlBuilder interface for the following systems:

• SQL Server
• MySQL - needs testing
• SQL Server CE - needs testing
• SQL Lite - needs testing
• Postgres - needs testing

No is possible install the package on ".Net Framework=4.5"

Hi Henk Mollema.

I'm trymng install the package into class library project that use ".Net Framework 4.5".

I'm using your project as an alternative to EntityTypeConfiguration for Dapper and would like to be able to map the name of my tables.

This can be considered as a feature or am using the wrong package?

Thank you for your initiative.
Great Job!

Oracle support

Do you intend to support Oracle?
I am currently using FluentMap and would like to add Dommel for CRUD. But at the moment only Dapper-Extensions seems to have Oracle support.
See https://github.com/tmsmith/Dapper-Extensions/blob/master/DapperExtensions/Sql/OracleDialect.cs

Dommel ignores DateTimeOffset properties

My table contains datetimeoffset(0) column and respected class has DateTimeOffset property. Current implementation ignores such properties. That is because PropertyResolverBase.PrimitiveTypes property does not contain DateTimeOffset type in its HashSet.
There is a way to resolve this issue by inheriting from DefaultPropertyResolver and override 'PrimitiveTypes' property.

public sealed class CustomPropertyResolver : DommelMapper.DefaultPropertyResolver
        {
            protected override HashSet<Type> PrimitiveTypes => new HashSet<Type>(new[]
                                                                                     {
                                                                                        typeof(object),
                                                                                        typeof(string),
                                                                                        typeof(Guid),
                                                                                        typeof(decimal),
                                                                                        typeof(double),
                                                                                        typeof(float),
                                                                                        typeof(DateTime),
                                                                                        typeof(DateTimeOffset),
                                                                                        typeof(TimeSpan)
                                                                                     });
        }

Then register CustomPropertyResolver somewhere in startup like 'DommelMapper.SetPropertyResolver(new CustomPropertyResolver());'

Would be great if @henkmollema fix it.
BTW, I'm using .NET Core 1.0.1 if it matters

Default resolver implemenations should be public

Build for .NET 3.5

oops..

Select with expressions

Add a Select(Expression<Func<TEntity, bool>>) method to the API which accepts an expression which is translated to sql code.

Foreign Key Problems

I'm having trouble working with a foreign key.

inside a class, I need to put the name of a property + "Id" so that it is recognized as foreign key.

This is not legal ..

Would not it be better to specify a foreign key at the time of mapping?

For example:

ToTable ("Component");
Map (m => m.Id) .Iskey ();
Map (m => Description);
Map (m => m.Product) .IsForeignKey (); // Suggestion

Add Linq support

Generate SQL queries from Linq expressions.

• http://msdn.microsoft.com/en-us/library/vstudio/bb546158(v=vs.110).aspx
• http://stackoverflow.com/questions/7731905/how-to-convert-an-expression-tree-to-a-partial-sql-query
• http://blogs.msdn.com/b/mattwar/archive/2008/11/18/linq-links.aspx

Perhaps this can be useful: http://relinq.codeplex.com/.

---

- [x] `Query` - the query object for `TEntity`, implements `IOrderedQueryable`. - [x] `QueryProvider` - implements `System.Linq.IQueryProvider` and serves as a base class for all providers for the `Query` class. - [x] `DommelQueryProvider` - derives from `QueryProvider`, used to generate queries for the `Query` class using a query translator. - [ ] Cache metadata in the `DommelQueryProvider`. - [x] `IQueryTranslator` - defines a contract for query translators. - [ ] `SqlQueryTranslator` - implements `IQueryTranslator` and derives from `System.Linq.Expressions.ExpressionVisitor`, uses `Visit-*` methods to build the sql query for the expression passed in the `Translate(Expression)` method.

SqlQueryTranslator should parse the following LINQ methods:

• Where
• Skip
• Take
• OrderBy
• OrderByDescending
• Count
• DommelMapperLinq - contains extension methods on the IDbConnection interface. Should also contain methods to pass in custom implementations for IQueryTranslator and IQueryProvider.
• DommelMapperLinq.Table(IDbConnection) - returns a new instance of the Query<TEntity> class which allows further querying using LINQ.

Support to retreive entity with composite key with params object[] key

Resolving foreign key properties

Hi,

In your implementation default of interface IForeignKeyPropertyResolver you only get the name of type + Id, but there is any property in the database that not follow this rule.

Maybe you can create a method in DommelEntityMap that set a name of FK Property and in the method ResolveForeignKeyProperty get this name or return this role above.

Thanks.

MultiMap and Inner Joins

The multimap feature writes always "inner joins", but sometimes you will need a "left join".
Perhaps verify if the reference property is a nullable, and in this case uses a left join.

Caching support

Add a simple, lightweight caching mechanism for retrieving entities.

Include with Get and GetAll

Add a simple include mechanism to the API which allows to include navigation properties.

PropertyResolverBase.FilterComplexTypes removes byte[]

PropertyResolverBase.FilterComplexTypes removes byte[] which is a valid SQL server type.

Conflict in select with inner join

Hi,

In my database there is many table that have columns with same name. When you use select * from the database return an exception.
Perhaps, you use the mapping for especification the name of column in the select clause.

Thank you for all.

Lock dictionary caches to prevent duplicate key insert

Currently all the query cache's are stored in dictionaries with

if (!dictionary.hasKey(type)) { //build sql based off of type and store in dictionary }

logic. Problems occur when more than on thread/request hit the same bit of code at the same time. Causes exceptions to occur (typical dictionary key + multi-threading issues).

Problem inserting to table with reserved column names

Hello,

I get SQL exceptions when I try to insert into the following table:

CREATE TABLE IF NOT EXISTS `CashLog` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `change` bigint(20) NOT NULL,
  `balance` bigint(20) NOT NULL,
  `use` varchar(255) NOT NULL,
  `time` datetime NOT NULL,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=latin1;

The generated SQL Query looks like this:
insert into CashLog (Username, Change, Balance, Use, Time) values ('test', 233, 1219, 'WITHDRAW', '2017-04-30 23:58:55')

The problem is, that the table uses preused names for the column names. So MySQL thinks I am trying to use a MySQL command instead of referencing the column name. If you use apostrophes for inserting, everything works as inteded. The working query looks like this:

insert into CashLog (`username`, `change`, `balance`, `use`, `time`) values ('test', 233, 1219, 'WITHDRAW', '2017-12-31 23:59:59')

It should be easily fixable by modifying the MySqlSqlBuilder return statement to include the ` apostrophe.

EDIT: I fixed the error on my fork and created a pull request. I am pretty new to C# so there may be a cleaner and/or better way :)

Treat Enum as primitive type with Select

Ignore on Property mappings do not works

When i'm trying to make a Crud with this classes and ignore property Friends on Person class mapping, the "ignore" don't works with Dommel.

Error on insert method:
"System.NotSupportedException: The member Friends of type Friend cannot be used as a parameter value".

public class Person{

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

  public List<Friend> Friends {get;set;}
}

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

Thanks in advance

Insert does not work for dommel in postgres with table name - TableName

"42P01: relation \"specialistportfolios\" does not exist"

My Table name is SpecialistPortfolios and dommel is not able to map automatically in insert postgres

Select method can support orderby ?

in most scenarios,'orderby' is required. can you enhance this feature?

Projections

Provide a mechanism to project subsets of tables in queries. For example:

Project<Foo>(x => new { x.Bar, x.Baz, x.Qux });

IDbTransaction into Select method

Method Select for predicate filters needs a transaction param.

Joins do not work correctly

When building the join script, the order of the generic type params is used to determine the source and including table. This obviously does not work.

Add IDbTransaction parameter to CUD operations

Dapper allows a transaction to be passed as parameter in the Execute method. Add this as optional parameter to the Insert, Update and Delete methods as well.

Specify a sequence name

Today is not possible to specify a sequence name in a key property.

Guid should be a primitive type

More advanced SQL builder class

Might be nice to have a more advanced SQL builder class for the following operations:

• Select all entities
• Select single entity
• Insert entity
• Update entity
• Delete entity
• Where-clause
• and/or-clause

Like

Hi,

is there a way to perform a like operation, such as contains?

the query i'm trying to achieve is this:
var result = _repository.GetList(r=> r.ImageName.Contains("yellow");

Sqlite Insert is wrong please use the ;

Hello , i tested your library which seems to become prety good. Your Sqlite code is wrong. pkease fill free to use mibne (MySqliteBuilderà) instead. You only need to set a semicolon between the insert and select.

I will had my test project, hope it will be hepfull.

public  class MySqliteSqlBuilder : Dommel.DommelMapper.ISqlBuilder
    {
        public string BuildInsert(string tableName, string[] columnNames, string[] paramNames, PropertyInfo keyProperty)
        {
            return string.Format("insert into {0} ({1}) values ({2}); select last_insert_rowid() id",
                tableName,
                string.Join(", ", columnNames),
                string.Join(", ", paramNames));
        }
    }

Regards

Passing parameters into `Get()`

I'm referring to the file DommelMapper.Get.cs which I have attached as a screenshot.
On line 86, the transaction is passed in as a parameter to Get<TEntity>() if ids.Length == 1.

However, on line 91, the transaction isn't passed in to QueryFirstOrDefault<TEntity>(). I think it's useful to pass the transaction in since it may have an isolation level of RepeatableRows or Serializable.

On the same note, is there a way for us to pass in the CommandTimeout as well?

Support joins with different relations

Currently not all relationships are supported whem joining columns, just 1:1.

• 1:1
• 1:n

Make resolve methods accessible via an helper class

Methods which use the replaceable resolver interfaces are now private. They should be publicly accessible so extensions for Dommel can use them too for query generation.

Insert() not works for postgresql

I think that last_insert_rowid is for SQL server, postgresql should use LASTVAL();

Exception Details:
{Npgsql.PostgresException: 42601: syntax error at or near "select"
   at Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage)
   at Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode)
   at Npgsql.NpgsqlConnector.ReadExpecting[T]()
   at Npgsql.NpgsqlDataReader.NextResultInternal()
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReaderInternal(CommandBehavior behavior)
   at Dapper.SqlMapper.ExecuteReaderWithFlagsFallback(IDbCommand cmd, Boolean wasClosed, CommandBehavior behavior)
   at Dapper.SqlMapper.<QueryImpl>d__1241.MoveNext()
   at System.Collections.Generic.List1..ctor(IEnumerable1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable`1 commandType)
   at Dommel.DommelMapper.Insert[TEntity](IDbConnection connection, TEntity entity, IDbTransaction transaction)
   at xxxx.Controllers.Api.BaseController.Create[T](T item) 
....
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionFilterAsync>d__28.MoveNext()}

Deploy v1.3.2

Skip complex properties for entities

Currently, every public property is used when building an insert/update query. Complex properties should be filtered from this. This issue is related to #13.

Fix: create an IPropertyResolver interface with a default implementation. The default implementation should skip complex properties of the type. (i.e.: only int, string, DateTime etc.)

Rename `Dommel` class to `DommelMapper`.

Class Dommel in namespace Dommel may cause issues. Rename class to DommelMapper.

Support Primary Key of type GUID retrieval on Insert

Actually if there is a Primary Key Id of type int,, the insert returns the newly server-generated int value when adding records.
Would be great to also support another type of Primary Key , Guid also server generated.
the fix can be done (in t-sql) with the same scope_identity function, avoiding to cast to int always but taking in account the Guid type

Support many-to-many relations

Method Select for support Transaction

There is already support for transaction in select commands?

Insert error - Cannot insert explicit value for identity column in table 'YouTableName' when IDENTITY_INSERT is set to OFF.

Cannot insert explicit value for identity column in table 'category' when IDENTITY_INSERT is set to OFF.

 private static string BuildInsertQuery(IDbConnection connection, Type type)
        {
            string sql;
            if (!_insertQueryCache.TryGetValue(type, out sql))
            {
                var tableName = Resolvers.Table(type);
                var keyProperty = Resolvers.KeyProperty(type);
                var typeProperties = Resolvers.Properties(type)
                                              .Where(p => p != keyProperty || keyProperty.GetSetMethod() != null)
                                              .Where(p => p.GetSetMethod() != null)
                                              .ToArray();

                var columnNames = typeProperties.Select(Resolvers.Column).ToArray();
                var paramNames = typeProperties.Select(p => "@" + p.Name).ToArray();

                var builder = GetBuilder(connection);

                sql = builder.BuildInsert(tableName, columnNames, paramNames, keyProperty);

                _insertQueryCache[type] = sql;
            }

            return sql;
        }

I think this line of code is a bug:

 .Where(p => p != keyProperty || keyProperty.GetSetMethod() != null)

The old version works fine;

Resolvers.Properties(type).Where(p => p != keyProperty).ToList();  

Invalid Column Map

Hello,
We have the trouble with Dommel.

We use: MySQL 5.6.15 + Dapper 1.50.2 + Dommel 1.6.0 + Dapper.FluentMap.Dommel 1.4.0

Our Model:

public class AppCityList
{
    public int Id { get; set; }
    public string CityName { get; set; }
    public int StateId { get; set; }
    public int CountryId { get; set; }
}

Our Mapping class:

public class AppCityListMap : DommelEntityMap<AppCityList>
{
    public AppCityListMap()
    {
        ToTable("app_city_list");
        Map(p => p.Id).IsKey();
        Map(p => p.CityName).ToColumn("City_Name");
        Map(p => p.StateId).ToColumn("State_Id");
        Map(p => p.CountryId).ToColumn("Country_Id");
    }
}

We have added mapper using next command:

FluentMapper.Initialize(config => { config.AddMap(new AppCityListMap()); });

We try to run query using next command:

private TU GetSingle<TU>(Expression<Func<TU, bool>> predicate)
{
    using (var con = ConnectionFactory.GetReadOnlyConnection())
    {
        var connection = con;
        return Action(con, () =>
        {
            var entities = connection.Select(predicate);
            return entities.FirstOrDefault();
        });
    }
}

public AppCityList GetCityByNameAndStateIdAndCountryId(String cityName, Int32 stateId, Int32 countryId)
{
    return GetSingle<AppCityList>(a => a.CityName == cityName && a.CountryId == countryId && a.StateId == stateId);
}

But our SQL looks like this:
select * from app_city_list where CityName = @p0 and CountryId = @p1 and StateId = @p2

And we have an error:
Unknown column 'CityName' in 'where clause'

This error occurs because only City_Name column exists in our table app_city_list.
Could you help with the problem of improper mapping work?