phnx47 / dapper-repositories Goto Github PK
View Code? Open in Web Editor NEWCRUD for Dapper
License: MIT License
CRUD for Dapper
License: MIT License
My POCO:
[Table("comments")]
public class Comment
{
[Key, Identity, Column("comment_id")]
public long Id { get; set; }
[Column("media_id")]
public long? MediaId { get; set; }
[Column("author_id")]
public long AuthorId { get; set; }
[Column("text")]
public string Text { get; set; }
[Column("is_private")]
public bool IsPrivate { get; set; }
[Deleted, Column("is_deleted")]
public bool IsDeleted { get; set; }
[LeftJoin("medias", "media_id", "media_id")]
public Media Media { get; set; }
[LeftJoin("users", "author_id", "user_id")]
public User Author { get; set; }
}
Linq expression:
await base.FindAllAsync<User>(x => !x.IsDeleted
&& (x.AuthorId == userId || x.IsPrivate == true), x => x.Author);
The following sql is generated:
SELECT comments.comment_id AS Id...
FROM comments LEFT JOIN users ON comments.author_id = users.user_id
WHERE comments.is_deleted = @IsDeleted OR comments.author_id = @AuthorId OR comments.is_private = @IsPrivate
I expected that the following SQL would be generated:
SELECT comments.comment_id AS Id...
FROM comments LEFT JOIN users ON comments.author_id = users.user_id
WHERE comments.is_deleted = @IsDeleted AND (comments.author_id = @AuthorId OR comments.is_private = @IsPrivate)
Is it something I'm doing wrong or is it a mistake?
If it's possible to add the function Count(predicate)? Thanks
Hi phnx47,
as this title I had this error many times on my web, although my website hasn't too much concurrent user but it has some long request. I don't think this error come from Dapper because Dapper open and close connection after each execution. I'm not sure but I think this error come from using only one instance for DapperRepository. I'm thinking about using DbProviderFactories to create new instance of connection for each execute command. How do you think about this ?
Hi, I am develop my project with MicroOrm.Dapper.Repositories, and that is something confuse me.
When I am use
carRepository.Delete(new Car { Id = 1 });
The sql generated is like this
But when I try to delete instance by linq expression, like this
carRepository.Delete( x => x.Id = 1);
The sql generated is different
DELETE FROM Car WHERE Car.Id = @id AND Car.DelFlag != -1 -- @id=1
It seem that the [Deleted] attribute is invalied when delete with linq expression, is that a bug or some specific reason?
And my entity
public class Car
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
[Status, Deleted]
public DeleteStatus DelFlag { get; set; }
}
public enum DeleteStatus
{
Inactive = 0,
Active = 1,
[Deleted]
Deleted = -1
}
I am trying to replicate the MsSqlRepositoriesTests but I received this runtime error: System.NotSupportedException: 'predicate can't parse'
[Table("TestTableSize")]
public class TestTableSize
{
[Column(TypeName = "varchar"), MaxLength(10)]
public string MyKeyField; // varchar(10), not null
[Column(TypeName = "datetime")]
public DateTime? MyDate1; // datetime, not null
}
--Code---
var testConnectionString = "Data Source=DOOM\SCOM;Initial Catalog=testAdminSQL;Integrated Security=True";
MsSqlDbContext db = new MsSqlDbContext(testConnectionString);
var test = db.TestTableSize.Find(x => x.MyKeyField == "0002720715"); // Exception
Hi,
I have class team
[Table("Teams")]
public class Team
{
[Key, Identity]
[Column("id")]
public int Id { get; set; }
[Column("city")]
public string City { get; set; }
[Column("address")]
public string Address { get; set; }
[Column("name")]
public string Name { get; set; }
[Column("stadium_name")]
public string StadiumName { get; set; }
[InnerJoin("Players", "id", "team_id")]
public virtual List<Player> Players { get; set; }
[Status]
[Column("is_deleted")]
public StatusDelete Deleted { get; set; }
}
and when i use contains for where clause:
var searchQueryForWhereClause = teamPostParameterModel.SearchQuery.Trim().ToLowerInvariant();
collectionBeforePaging = (await DbContext.TeamRepository.FindAllAsync<Player>(
x => x.Name.ToLowerInvariant().Contains(searchQueryForWhereClause),
y => y.Players)).ToList();
I have issue
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware[0]
An unhandled exception has occurred while executing the request
System.NotImplementedException: predicate can't parse
at MicroOrm.Dapper.Repositories.SqlGenerator.SqlGenerator`1.FillQueryProperties(Expression expr, ExpressionType linkingType, List`1& queryProperties)
at MicroOrm.Dapper.Repositories.SqlGenerator.SqlGenerator`1.AppendWherePredicateQuery(SqlQuery sqlQuery, Expression`1 predicate, QueryType queryType)
at MicroOrm.Dapper.Repositories.SqlGenerator.SqlGenerator`1.GetSelect(Expression`1 predicate, Boolean firstOnly, Expression`1[] includes)
at MicroOrm.Dapper.Repositories.DapperRepository`1.FindAllAsync[TChild1](Expression`1 predicate, Expression`1 tChild1, IDbTransaction transaction)
at Business.implements.ManagerTeamAndPlayerBusiness.<GetTeams>d__1.MoveNext() in D:\football-league-schedule\Business\implements\ManagerTeamAndPlayerBusiness.cs:line 30
How to fix it??
When we type something like
repo.FindAll(x => x.Field == "a" || x.Field == "b")
SqlGenerator translates it into
SELECT t.Field FROM t WHERE t.Field = @Field OR t.Field = @Field
then '@field' replaces only with "a" and skips "b" because there is only one parameter in sql query, not two.
we should transform predicate into
SELECT t.Field FROM t WHERE t.Field = @Field_1 OR t.Field = @Field_2
Would you please provide a complete Demo, especially the part of the "Find" interface Demo?
The "Find.Join" interface has not found the usage description.
Bug with using nested entities for FindAll.
Hi,
I am unable to retrive the nested entity except first object.
When use FindAll. For example in case with composite KEY.
FindAll(predicate, rt => rt.BidRiskRateValues, transaction);
[Table("BidRiskRateValues", Schema = "CONT")]
public class BidRiskRateValue
{
[Key]
[Column(Order = 1)]
public System.Guid BidRiskRateId { get; set; }
[Key]
[Column(Order = 2)]
public short Year { get; set; }
/// <summary>
/// Parent BidRiskRate pointed by [BidRiskRateValues].([BidRiskRateId]) (FK_BidRiskRateValues_Id)
/// </summary>
[InnerJoin("BidRiskRates", "BidRiskRateId", "Id", "CONT")]
public virtual BidRiskRate BidRiskRate { get; set; }
}
[Table("BidRiskRates", Schema = "CONT")]
public class BidRiskRate
{
[Key]
public Guid Id { get; set; }
public int BidId { get; set; }
/// <summary>
/// Child BidRiskRateValues where [BidRiskRateValues].[BidRiskRateId] point to this entity (FK_BidRiskRateValues_Id)
/// </summary>
[LeftJoin("BidRiskRateValues", "Id", "BidRiskRateId", "CONT")]
public virtual System.Collections.Generic.ICollection<BidRiskRateValue> BidRiskRateValues { get; set; }
}
AR:
Get only first nested object.
ER:
Get collection of nested entities.
Hi, Author
Can we specify TableSchema in Table attribure or other attribute ?
If possible put an example .
I work with table names with numbers. Is there a more elegant way to have brackets than injecting them in the query object?
Is the author ever hoping to update it?
i get en error when i try to update an entity with GUI id. (error after where clause )
using MySQL.Data.dll 6.9.9.0
using Dapper 1.50.2.0
using MicroOrm.Dapper.Repositories 1.4.2.0
Issues: Cannot read field values for blob MySQL type , use FindAsync() method.
can't find Ms Access support in this project.
In C# 5 the only way using auto properties is below. The "repositories" should recognize that as "no setter". Therefore "read only" feature does not work.
public DateTime CreateDate { get; private set; }
http://stackoverflow.com/questions/27781522/read-only-public-properties-without-setters-getters
Hi
I am unable to retrieve the nested entity in join select.
User Entity has list of cars and car contains another entity Status.
I would like to provide a username and get all cars along with its status. I am not able to get this to work.
User => List(Car) => Status
I am using like (similar way Entity Framework allow include for nested entities)
ISqlGenerator userSqlGenerator = new SqlGenerator(SqlConnector, true);
userSqlGenerator.GetSelectAll(null, user => user.Cars, user => user.Cars.Select(t => t.Status) );
Please let me know how can I achieve this ?
Thanks
test:
List keyList = new List { 2, 3, 4 };
var users = (await _sqlDatabaseFixture.Db.Users.FindAllAsync(x => keyList.Contains(x.Id))).ToArray();
is true;
but I try:
List batchnumbers=new List();
var orderlist = msSqlDbContext.Orders.FindAll(o =>
batchnumbers.Contains(o.BatchNumber)
);
it's : Only one degree of nesting is supported;
MicroOrm.Dapper.Repositories.SqlGenerator.ExpressionHelper.GetPropertyName(BinaryExpression body, Boolean& isNested)
MicroOrm.Dapper.Repositories.SqlGenerator.SqlGenerator1.FillQueryProperties(BinaryExpression body, ExpressionType linkingType, List
1& queryProperties)
MicroOrm.Dapper.Repositories.SqlGenerator.SqlGenerator1.GetSelect(Expression
1 predicate, Boolean firstOnly, Expression1[] includes) MicroOrm.Dapper.Repositories.SqlGenerator.SqlGenerator
1.GetSelectAll(Expression1 predicate, Expression
1[] includes)
MicroOrm.Dapper.Repositories.DapperRepository1.FindAll(Expression
1 predicate, IDbTransaction transaction)
Not everyone wants or needs UTC in their database. Make this configurable through the attribute value. Currently it always defaults to UTC which does not play well with other related columns. It does not make sense to set CreateDate manually as local, then the framework sets UpdateDate to something completely different.
public virtual SqlQuery GetInsert(TEntity entity)
...
if (HasUpdatedAt)
UpdatedAtProperty.SetValue(entity, DateTime.UtcNow);
At line 131 in the SqlGenerator.cs file
if (Config.SqlConnector == ESqlConnector.MySQL || Config.SqlConnector == ESqlConnector.PostgreSQL)
sqlQuery.SqlBuilder.Append("LIMIT 1");
one can see that a space is missing such that "LIMIT 1" should be " LIMIT 1" otherwise it returns ...@IdLIMIT 1
resulting in an SQL syntax error.
Saw a fix for an issue with FindAll using more than three auto join properties: 0f60e30
Wondering if there's an ETA for this fix making it up on Nuget?
can you give a page method. it is always use.
Hello, author! When do you support the Oracle database? Thank!!
I am trying to use [InnerJoin] attribute and I cant get it to work. Could you provide an example of how to make this work?
await _repo.FindAsync(e => e.Token == "token");
When I do this, I get key not found:
await _repo.FindAsync(e => e.Token == "token", u => u.Username);
The error that I am getting is: Unable to cast object of type MethodBinary to MemberExpression
My model looks something like this. CustId and Id are GUID. Id is the key of customer table...
Note username property doesn't exist in CustomerActivation table.
[InnerJoin("Customer", "CustId", "Id")]
public string Username { get; set; }
What am I missing?
[LeftJoin("tblFoo", "Foo1ID", "ID")]
public Foo Foo1 { get; set; }
[LeftJoin("tblFoo", "Foo2ID", "ID")]
public Foo Foo2 { get; set; }
....
repository.FindAll<Foo, Foo>(x => x.ID > 0, x => x.Foo1, x => x.Foo2)
Results in
The objects "tblFoo" and "tblFoo" in the FROM clause have the same exposed names. Use correlation names to distinguish them.
Is it possible to overcome this?
(btw, it would be really nice to be able to get the whole table with children without predicate)
var result = _repository.FindAll(u=>u.Nom==q || q ==null);at MicroOrm.Dapper.Repositories.SqlGenerator.SqlGenerator1.FillQueryProperties(Expression expr, ExpressionType linkingType, List
1& queryProperties)
at MicroOrm.Dapper.Repositories.SqlGenerator.SqlGenerator1.FillQueryProperties(Expression expr, ExpressionType linkingType, List
1& queryProperties)
at MicroOrm.Dapper.Repositories.SqlGenerator.SqlGenerator1.GetSelect(Expression
1 predicate, Boolean firstOnly, Expression1[] includes) at MicroOrm.Dapper.Repositories.SqlGenerator.SqlGenerator
1.GetSelectAll(Expression1 predicate, Expression
1[] includes)
at MicroOrm.Dapper.Repositories.DapperRepository1.FindAll(Expression
1 predicate, IDbTransaction transaction)
at App.UI.Controllers.Api.v2.ms.UserController.Get(String q) in C:\Users\m_boudchicha\Documents\Visual Studio 2015\Projects\App.UI\App.UI\Controllers\Api\v2\UserController.cs:line 28
at lambda_method(Closure , Object , Object[] )
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.b__9(Object instance, Object[] methodParameters)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)
Hi,
I am getting exception when my entities object having relationship mapping with different field names.
like.
[Table("Cars")]
public class Car
{
[Key, Identity]
public int CarId { get; set; }
public string Name { get; set; }
public int UserId { get; set; }
[LeftJoin("Users", "UserId", "UserId")]
public User User { get; set; }
[Status]
public StatusCar Status { get; set; }
}
[Table("Users")]
public class User
{
[Key, Identity]
public int UserId { get; set; }
public string ReadOnly => "test";
public string Name { get; set; }
[LeftJoin("Cars", "CarId", "UserId")]
public List<Car> Cars { get; set; }
[Status, Deleted]
public bool Deleted { get; set; }
[DateChanged]
public DateTime? UpdatedAt { get; set; }
}
It will throw spitOn parameter exception.
Get(m => m.Id == 700 && m.UserName.Contains("userName")) Do not support the 'like' SQL syntax
Is there a way to map a many-to-many relationship via a relation table?
Hi,
I am using NuGet Package version 1.7.1.
When inserting a record using the repo.Insert method I get the following error:
Object reference not set to an instance of an object.
However the repo.InsertAsync method works fine.
Please assist.
Using the following prerequisite:
var _ctx = new DapperRepository(new SqlConnection(connectStr), ESqlConnector.MSSQL);
Please consider the following 2 statements:
var result = _ctx.FindAll(x => x.MachineLinkID == 1 && x.DateStampUp == null && x.OperatorID == null);
var result = _ctx.FindAll().Where(x => x.MachineLinkID == 1 && x.DateStampUp == null && x.OperatorID == null);
The first one returns a result count of 0 while the second one returns the expected results...
What am I missing here?
I am using your library with Sql Server 12.
It's include MSSQL/MySQL/PgSQL support in ESqlConnector, but I can't find SQLite support in this project.
SELECT GenericAttribute.EntityId, GenericAttribute.KeyGroup, GenericAttribute.Key AS Key, GenericAttribute.Value, GenericAttribute.StoreId, GenericAttribute.Id FROM GenericAttribute
use: dapperRepository.FindAll()
ex:There is a syntax error near the keyword'Key'.
I have a problem with generate sql.
if we set in DapperRepository constructor :
new SqlGeneratorConfig()
{
SqlConnector = ESqlConnector.PostgreSQL,
UseQuotationMarks = true
}
and then execute :
await base.InsertAsync(dbEntity).ConfigureAwait(false)
we will have sql like this :
INSERT INTO "Blogs"("Name") VALUES (@name)RETURNING Id
but expected result :
INSERT INTO "Blogs"("Name") VALUES (@name)RETURNING
"Id"
the same behaviour and for MsSql :
INSERT INTO [Blogs]([Name]) VALUES (@name)SELECT SCOPE_IDENTITY() AS Id
but expected result :
INSERT INTO [Blogs]([Name]) VALUES (@name)SELECT SCOPE_IDENTITY() AS
[Id]
PS^ This important because pgsql not case sensitive and UseQuotationMarks = true, very helpful. In another case we need add ColumnAttribute for example : [Column("\"Id\"")] to all property, with corresponding names, in class to achieve correct behaviour and turn off UseQuotationMarks.
is it codefist?
I use Mycat 1.6 proxy service. current mysql 5.7,
AspNet core 1.1 reference Pomelo.Data.Mycat driver 1.0,
invoke insert() method throw System.NullReferenceException: Object reference not set to an instance of an object.
559 line :var newId = Connection.Query(queryResult.GetSql(), queryResult.Param, transaction).FirstOrDefault();
use ExecuteScalar() fix bug ,please.
I want implement Unit of Work Pattern for MicroOrm.Dapper.Repositories, how to do ?? Can U help me? Thanks you!
Hi phnx47,
I used your lib for my project with PostgreSQL and it worked well except a situation when I used Contains expression for query all items have id in collection like this:
string[] id_arr = ids.Split(','); List<int> loctypeIds = new List<int>(id_arr.Select(int.Parse).ToArray()); return Json(_subtypeRepository.FindAll(x => loctypeIds.Contains(x.loc_type_id)).ToList(), JsonRequestBehavior.AllowGet);
and this is my model:
[Table("category.location_subtypes")] public class LocationSubtype { [Key] [Identity] public int id { get; set; } public int loc_type_id { get; set; } [StringLength(100)] public string description { get; set; } }
I got error message like this:
42703: column location_subtypes.controllers does not exist
I know this error from PostgreSQL when it can not find out column with name "location_subtypes.controllers" but I don't know why query has this column name.
Do you have any idea ? Thank you.
Using the built-in FindAsync<ChildT>(..)
method, the SqlGenerator does not include columns in the ChildT when they have both [Key]
and [Column(Order = ##)]
attributes. The rest of the join attribute and generated SQL appear correct. The projection generates the correct number of selects, just missing the column names.
Ex:
On v1.5 Output is similar to:
SELECT [Table1].[Column1], ... [Table1].[Column12], Table2., Table2., FROM ...
ON v1.6 Beta 3 Output is similar to
SELECT [Table1].[Column1], ... [Table1].[Column12], [Table2].[], [Table2].[], FROM ...
When i do:
public bool UpdateBulkRoute(List<RouteEntity> updateRoute)
{
try
{
return BulkUpdate(updateRoute);
}
catch (Exception ex)
{
throw new Exception("Error updating route.", ex);
}
}
it gives me this error "{"42601: syntax error at or near "UPDATE""}"
I'm using postgres as database ( ESqlConnector providerType = ESqlConnector.PostgreSQL;
).
While doing the BulkInsert in the same way, i got no error.
line 430 and line 467, the word "LIMIT" should start with " "
hi phnx47,
this is a great Repositories for dapper ,I want to know how to use procedure in this Repositories?
can you help me ? thank you .
When accessing an Oracle database, it is not supported
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.