henkmollema / dommel Goto Github PK
View Code? Open in Web Editor NEWCRUD operations with Dapper made simple.
License: MIT License
CRUD operations with Dapper made simple.
License: MIT License
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()}
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).
Add the GetAll()
method which will return all the objects in the table.
Add a Select(Expression<Func<TEntity, bool>>)
method to the API which accepts an expression which is translated to sql code.
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();
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.
How to insert List
Add a simple include mechanism to the API which allows to include navigation properties.
Method Select for predicate filters needs a transaction param.
Add a simple, lightweight caching mechanism for retrieving entities.
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 :)
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:
[Table]
attribute[Column]
attribute[ForeignKey]
attribute - #72OrderBy
#56 )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?
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?
Provide a mechanism to project subsets of tables in queries. For example:
Project<Foo>(x => new { x.Bar, x.Baz, x.Qux });
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!
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.
I would like to get the query built?
Generate SQL queries from Linq expressions.
Perhaps this can be useful: http://relinq.codeplex.com/.
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.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 DommelEntityMa
p that set a name of FK Property and in the method ResolveForeignKeyProperty
get this name or return this role above.
Thanks.
One should be able to configure a custom column mapping for an entity property which should be used in every CRUD operation.
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");
"42P01: relation \"specialistportfolios\" does not exist"
My Table name is SpecialistPortfolios and dommel is not able to map automatically in insert postgres
in most scenarios,'orderby' is required. can you enhance this feature?
Today is not possible to specify a sequence name in a key property.
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
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.
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.
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
Might be nice to have a more advanced SQL builder class for the following operations:
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
Right now the sql builders just don't insert the key property
Class Dommel
in namespace Dommel
may cause issues. Rename class to DommelMapper
.
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
There is already support for transaction in select commands?
PropertyResolverBase.FilterComplexTypes removes byte[] which is a valid SQL server type.
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.
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
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.)
Currently not all relationships are supported whem joining columns, just 1:1
.
1:1
1:n
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.