moonstorm / fastcrud Goto Github PK
View Code? Open in Web Editor NEWfast .NET ORM for strongly typed people
License: MIT License
fast .NET ORM for strongly typed people
License: MIT License
It would be nice to have a way of updating portions of an entity, in case the entity gets partially updated from a DTO.
There is a issue. I must add order by for offset and fetch.
SqlBuilders/MsSqlBuilder.cs
There is fix
public override string ConstructFullBatchSelectStatement(
FormattableString whereClause = null,
FormattableString orderClause = $"1",
int? skipRowsCount = null,
int? limitRowsCount = null,
object queryParameters = null)
Hello Again
I have a piece of code like this:
[Table("[Report.Schedule]")]
public partial class Report_Schedule
{
// ... other columns
[Column("SCHEDULE"), Required]
public string SCHEDULE_DETAIL { get; set; }
}
So the column SCHEDULE would save, but the property SCHEDULE_DETAIL would never load the data from database. If however, I change the property name back to "SCHEDULE", it would read just fine.
Here is the Column create SQL:
[SCHEDULE] [nvarchar](max) NOT NULL,
The Find method is similar to EF Where method. Which always returns an empty IEnumerable (not nulll) when there are no results that match that query.
But here in FastCrud null is returned which means on every Find we have to do a null check. Should we be returning IEnumerable with no elements instead?
Hello, how are you?
First congratulations on the excellent work.
You can add the library one strongname?
How do I sign my library, I can not use his because it is not signed and is thus triggered an exception.
Thank you.
Hello,
please can you tell me an example in vb.net.
Im new in C# and don't undertand this. Btw: This example dosen't work in Net 4.5 for me.
I get an error when i call the LastInsertRowId from SQLiteConnection after your insert methode.
"System.InvalidOperationException: Database connection not valid for getting last insert rowid."
My code :
public T Insert(T item) where T : IPropertyId
{
using (var dbConnection = SimpleDbConnection())
{
dbConnection.Insert(item);
item.Id = dbConnection.LastInsertRowId;
return item;
}
}
Do you close the connection when we call your Insert method ?
I setup a simple unit test that adds a row to the database and the date that is added to the database has Kind property set when inserting it as Utc by default. As its added using DateTime.UtcNow.
When i get the same object back from Database using dapper / dapper fastcrud the same field has the correct datetime but the Kind property is Unspecified - when it should be Utc.
I realize that this could be something that maybe need to be put in dapper. Is there any overrides we can do to fastcrud library that can set the kind property to the datetimes retrieved?
I am working on a project in ASP.NET Core. Can you make a package without the dependency on StringInterpolationBridge because it is not compatible with .NET Core 5.0 and makes the whole Dapper.FastCRUD package unusable when trying to play around with it in e.g. ASP.NET Core ?
When you map the property to the column with same name using SetProperty with one param like this:
OrmConfiguration.RegisterEntity<Category>()
.SetTableName("Category")
.SetProperty(c => c.Code, prop => prop.SetPrimaryKey(true))
.SetProperty(c => c.Description);
Problem located in the EntityMapping.cs, line 197:
at Dapper.FastCrud.Validations.Requires.NotNull[T](T value, String parameterName)
at Dapper.FastCrud.Mappings.EntityMapping`1.SetProperty[TProperty](Expression`1 property, Action`1 propertySetupFct)
I got this exception:
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[ID],[CompanyID],[FirstName],[LastName] FROM [Persons] WHERE [ID]=4' at line 1
I tried latest nuget package MySql.ConnectorNET.Data by Oracle (v. 6.8.3.2) and latest 6.9.9.
Any suggestions ?
I have a property
public object FIELD_VALUE { get; set; }
which maps to a DB column FIELD_NAME which is of type sql_variant not null.
That column gets ignored when the insert or update statement is generated.
Works fine in select statements.
Btw. love what you are doing and thanks for the benchmarks. I had started using SimpleCRUD. When i stumbled upon this.
My question is would those entities directly work or do i need to regenerate them using the t4 templates you have provided?
In SimpleCRUD just having an Id named filed was enough. Do i have to decorate all Ids with
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
Thanks,
Mohit
Hi
Thanks for the great package.
Are you exposing you methods to get teh key names and values in any way.
I.e. can i call a class / method to get the primary key from your package. Or only by reflections?
var keys = OrmConfiguration.GetDefaultEntityMapping().GetProperties(PropertyMappingOptions.KeyProperty);
var allKeyValues= keys.Select(key => entity.GetType().GetProperty(key.PropertyName).GetValue(entity));
I update to from 2.1.2.to 2.2.0. then all Extensions marked as obsolate. What's wrong?
Hi,
How can I construct my queries with the statement builder to use the LIMIT
clause?
E.g.
connection.Find<Entity>( st => st.Limit( 100 ) ); // select * from entity limit 100
I've been through the code and wiki, but I can't find a solution. What am I missing here?
Thanks.
it would be useful for the library to provide help with generating manual SQL queries. At the very least it should provide help with the generation of table names and list of properties mapped to database column.
Alias support should also be included.
When I try to use the Query/QueryMultiple-Method of Dapper alongside with FastCrud the column mappings are not working. If I use SqlMapper.SetTypeMap to set up the mappng for Dapper, the column mappings for FastCrud are not working anymore and the Find-Method returns unpopulated entities.
How do I get around the issue?
Hi,
i really liked this library on top of dapper among other options.
I have seen mapping instruction which allows to update specific properties. is it possible to update properties from anonymous object or provide a list of properties at runtime?
.Update({Id= 1, Name= "ABC"});
.Update(user, statement => statement.onlyfields(u => u.Name));
this approach will be simple as OrmLite approach but that library is no more free to use.
For Paging, i have not seen any helper .Paging(1, 10, sql)
I have a plan to use this library but my project update same entity in different ways and having custom mappings for every case is not a case so is it possible to add this as feature in future if it makes sense?
I'm having trouble getting your example code to work from the wiki under the 'Joins' section. I've created the relevant database objects, etc. I have a single workstation that has multiple employee's. However, when executing the following code:
con.Get(new Workstation {WorkstationId = workStationId },
statement => statement.Include<Employee>()));
An exception is thrown:
Sequence contains more than one element
Stack trace if useful:
at System.Linq.Enumerable.SingleOrDefault[TSource](IEnumerable`1 source)
at Dapper.FastCrud.SqlStatements.RelationshipSqlStatements`1.SelectById(IDbConnection connection, TEntity keyEntity, AggregatedSqlStatementOptions`1 statementOptions)
at DapperCore.Repository.Repository.GetWorkstation(Int32 workStationId) in C:\W\DapperCore\DapperCore\src\DapperCore\Repository\Repository.cs:line 28
at DapperConsole.Program.Main(String[] args) in C:\W\DapperCore\DapperCore\src\DapperConsole\Program.cs:line 23
It doesn't seem to be able to handle the fact that the Workstation
has many Employee
objects associated with it.
If I remove the Employee's from the database so that there is only a one-to-one relationship between the two. The code above works fine and I get only a single Employee
object in the collection.
OrmConventions.cs:
Missing call to propertyMapping.ExcludeFromInserts().ExcludeFromUpdates(); for Computed option as well, otherwise the Update, Insert commands fail.
if (databaseGeneratedAttributes.Any(dbGenerated => dbGenerated.DatabaseGeneratedOption == DatabaseGeneratedOption.Computed))
{
propertyMapping.SetDatabaseGenerated();
}
if (databaseGeneratedAttributes.Any(dbGenerated => dbGenerated.DatabaseGeneratedOption == DatabaseGeneratedOption.Identity))
{
propertyMapping.SetDatabaseGenerated();
propertyMapping.ExcludeFromInserts().ExcludeFromUpdates();
}
Hi
I feel that the Sql Dialect is a problem.
If using a repository pattern, the base class uses a generic in regard to the entity, and probabley even the connectin is a generic.
So It is typically only when a method is called in through the repository to a database for an entity that the databse dialect is known. That is, if you have a flexible repository pattern.
What i am getting at, is it i very difficult to set the dialect without a helper method always being called in the repository methods, or always passing the mapping to the FastCRUD extension methods.
Is there really no way the the dialogue can be set a more automatic way, for example using the dbconnection type name?
Thanks for the great package :-)
I want to do a batch delete.
But there is no way to set the where clause in IStandardSqlStatementOptionsSetter.
The ISqlStatementOptionsGetter has a way to read the where.
Am i missing anything? I have not really gone into the sql generation logic much in this library yet.
It's not really an issue, it's just a possible enhancement. When i find some time, i'll see if i can do it on a fork and create a pull request for it. What i want to do is the following:
In a certain situation, i would like to log the generated sql query to the console.
When i have difficulties with dataqueries, i can setup sql profiler on MSSQL to see what sql query Fastcrud has generated, but it is not always possible to setup sql profiler(example sqlite). It would be great if we could have a way to see which sql query is send to Dapper to be executed.
Hi,
Am having an issues with conflicts between StringInterpolationBridge and the actual available classes in .Net framework 4.6.1
Error CS0433: The type 'FormattableString' exists in both 'StringInterpolationBridge, Version=0.9.1.0, Culture=neutral, PublicKeyToken=null' and 'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' (174, 27)
Given that Dapper.FastCrud is dependant on StringInterpolationBridge I cant remove it.
Any idea what I can do? I would prefer not to downgrade to 4.5.2 unless absolutely required.
Regards,
I did not see async methods in FastCRUD. Do you have those overloads? Any plans to add them?
Given that FastCRUD's focus is on speed.
Providing async overloads would help achieve more efficient use of server resources.
Hello,
I use your extension with sqlite.
I have an issue when the insert method is call : no such function: SCOPE_IDENTITY.
The connection :
SQLiteConnection($"Data Source={DbFile};Version=3;");
The table is :
@"create table Fournisseur
(
Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
Nom varchar(100) not null,
Pays varchar(100) not null
)"
Entity :
[Table("Fournisseur")]
public class Fournisseur
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }
[ExcelColumn("Nom")] //maps the "Name" property to the "Company Title" column
public string Nom { get; set; }
[ExcelColumn("Pays")] //maps the "State" property to the "Providence" column
public string Pays { get; set; }
}
Then
var fournisseur = new Fournisseur() { Nom = "AllInBIke", Pays = "France" };
db.Insert(fournisseur);
I have missing something ?
Allow for multiple dialects per entity domain set.
+1 for an awesome dapper extension. The code is pretty intense, complex and elegant at the same time. I'm still having a hard time trying to read it and understand the mappings, interfaces, builders, options, etc., hence these questions...
Find()
extension method?select ...
from tbl
where id = @id and isvirtuallydeleted = 0
BulkUpdate()
/BulkDelete()
is the answer?update tbl
set name = @name, concurrencyflag = @concurrencyflag
where id = @id and concurrencyflag = @originalflag and isvirtuallydeleted = 0
I've read through the other dapper extensions code and they don't support these two scenarios "by id" but there is a work around for the 1st one just like how I have described to use Find()
.
How i get complex models or how can i work with virtual model propertiies. There is no documentation about it or i cant find something.
Have you thought about selecting certain columns only in select / get queries?
I was thinking of having an attribute which eliminates certain columns from a get query. The use case i am trying to solve is to eliminate large fields which will slow down select queries. So the query is 10x faster if the big ntext of nvarchar(max) field can be ignored.
But when you are getting a single object you can include that field as part of the get.
What would you recommend be the best way to accomplish that?
Currently if i want to get just the first row, based on any where clause. I send skip = 0 and top/limitResults = 1.
But Skip throws an exception that skip should be > 0 instead of >=0.
/// <summary>
/// Skips the initial set of results.
/// </summary>
public TStatementOptionsBuilder Skip(long skipRecordsCount)
{
Requires.Argument(skipRecordsCount > 0, nameof(skipRecordsCount), "The number of records to skip must be a positive value");
this.SkipResults = skipRecordsCount;
return this.Builder;
}
Next, if i want to get first n rows then i have to set Top( n), that it using MsSqlbuilder causes a sql exception due to the fact that it prints out a fetch n without an offset.
HI
If i try e.g. try to change the dialect to Sqlite of an entity (i.e. not clone) and:
OrmConfiguration.GetDefaultEntityMapping().SetDialect(dialect);
and then:
connection.Insert(entity);
The dialog does not change and it tries to use MsSql dialect.
But if i:
transaction.Connection.Insert(entity, statement => statement.WithEntityMappingOverride(newMapping));
it works.
Update, Get etc. all apear to work, but that could be because the query is the same.
OrmConfiguration.GetDefaultEntityMapping().SetDialect(dialect); Set the dialog for all queries using that entity?
BR Rik
I have a C# class Sample
, one of whose properties is an enum, TargetType
. I have a corresponding table samples
defined in a PostgreSQL database, along with a matching enum type, targettypes
.
With Dapper.FastCRUD, I can retrieve records from the table successfully. However, I get an error during insertion:
Npgsql.PostgresException (0x80004005): 42804: column "target_type" is of type targettype but expression is of type integer
How do I tell Dapper.FastCRUD to map the C# enum TargetType to PostgreSQL targettype for inserts?
In my C# code, I've defined:
// Map the C# enum 'TargetType' to PostgreSQL enum 'targettype'
// Allows Npgsql to insert and retrieve the fields with correct type-casting / boxing
Npgsql.NpgsqlConnection.MapEnumGlobally<TargetType>("public.targettype");
Dapper.FastCrud.OrmConfiguration.DefaultDialect = Dapper.FastCrud.SqlDialect.PostgreSql;
When using only Npgsql (and no ORM), I can successfully insert as follows:
using (var writer = conn.BeginBinaryImport(sql))
{
foreach (Sample sample in entities)
{
writer.StartRow();
...
writer.Write(sample.TargetType); // <--- mapped to column 'target_type'
...
}
}
It is necessary to have OrmConfiguration.GetDefaultEntityMapping(Type).
Hello, I was attempting to use your repository with a Mysql database. I have an entity which has a composite primary key of which none of the keys are database generated. There is however a ModifiedTimestamp, which is calculated on insert and update. Sample Here:
public class AggregatedRow
{
[Key]
public int Id1{ get; set; }
[Key]
public string Id2{ get; set; }
[Key]
public string Id3 { get; set; }
public int AggregateValue { get; set; }
[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
public System.DateTime ModifiedTimestamp { get; set; }
}
Which is intended to correlate to the following table:
CREATE TABLE `AggregatedRow` (
`Id1` int(11) NOT NULL,
`Id2` varchar(255) NOT NULL,
`Id3` varchar(55) NOT NULL,
`AggregateValue` int(11) NOT NULL,
`ModifiedTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`FileCollectionCycleId`,`StatusName`,`EventTimestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
When I attempt to insert into this table with FastCRUD, I get:
Entity 'AggregatedRow' has database generated fields that don't contain a primary key.
Which looks like it is getting thrown from MySqlBuilder.ConstructFullInsertStatementInternal. It looks like my entity has a value of 0 for: InsertKeyDatabaseGeneratedProperties. Looking at the following code, it looks like those checks could be against KeyProperties == 0 instead. I'd make the pull request myself, but I'm not entirely sure if there's something I am missing.
If there's anything I can do to help, please let me know.
Also, thanks for the great work on this repo!
I am guessing the same will happen with updates and deletes.
This ORM for node js ran into the same problem with mssql which i saw upon googling.
The exact error is -
The target table 'Contact' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
There seems to be an issue with the GetAsync() method. I'm writing a small library to maintain the database of my private mail server and tried to query a SQLite database. The "MailAccount" class contains a property to the "Domain" the mail account belongs to. Furthermore a property of type long annotated with the ForeignKey attribute points to the "Domain" property.
The following line works as expected:
MailAccount account = connection.Get(new MailAccount { Id = id }, statements =>statements.Include<Domain>(x => x.LeftOuterJoin()));
The asynchronous equivalent does not:
MailAccount account = await connection.GetAsync<MailAccount>(new MailAccount { Id = id }, statements => statements.Include<Domain>(x => x.LeftOuterJoin()));
Is there something I should additionally consider when using the asynchronous API functions?
I see that entity having composite key for a SQLite database does not work.
There is a exception throw in the code.
Can you let me know how can I test my code with the SQLite and if there is a better alternative to do this
The new sql builders interfaces do not take null values.
Not sure if i am missing anything but this will end up being very verbose if i have to check whether skip that is passed to me is NULL or not and then call the .Skip() method vs me just being able to pass the values to the old sql generator.
And so and so forth with each of the inputs - order, take, where, transaction etc.
Hi
Very nice package. Great work! One issue. Insert should return an id. Pref a generic if possible.
Check: Dapper.Contrib Dapper.SimpleCRUD.
Seams to be a common requirement.
Great work.
Is there any support for .NET Core ?
Hi - I have a project with entity framework code first entities, generated from an existing database by reverse engineering a model, want to start using the same entities to use a few queries to go through FastCrud. Not sure if you have tried that.
What changes do you think i would need to make to the entities, to make it work with both?
Would the navigational properties (the public virtual ICollection properties for foreign keys) cause any issues with fastcrud?
wrapped the home examples in code blocks as the angle brackets weren't visible (ie dbConnection.BulkDelete();)
Added headings.
I couldn't figure out how to submit wiki updates as a patch, sorry.
Hello,
I've encounter a 'sequence contains no elements' exception when trying to Update an entity. Any help would be appreciated.
using (var connection = GetOpenConnection()) { connection.Update(hmsJobEntity); }
`public class HmsJob
{
[System.ComponentModel.DataAnnotations.Key]
public int Id { get; set; }
public string Name { get; set; }
public virtual ICollection HmsJobSetting { get; set; }
}`
`public partial class HmsJobSetting
{
[System.ComponentModel.DataAnnotations.Key]
public int Id { get; set; }
[System.ComponentModel.DataAnnotations.Schema.ForeignKey("HmsJob")]
public int HmsJobId { get; set; }
public string Name { get; set; }
public string Value { get; set; }
public string Comments { get; set; }
public virtual HmsJob HmsJob { get; set; }
}`
My needs (which does not seem uncommon) require me to talk to different databases, identical in schema, but having different schema names. This is a web service so could be many different threaded users. OrmConfiguration is static, and its the only way I know of to change the schema used on queries.
It seems like a common trend for these microOrms to use static facilities in such a way that makes them useless in non-trivial multi-tenant environments. I'm hoping this is not the case, I like your project.
Currently the library uses only auto-generated mappings based on attribute decorated entities. There is no way to currently override these defaults or provide manual mappings.
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.