EFCoreExtend
Entity Framework Core extension library
Nuget Download
PM> Install-Package EFCoreExtend
QueryCache use Redis£º
PM> Install-Package EFCoreExtend.Redis
Features
NonQuery: Execute Sql
DbContext db = new MSSqlDBContext();
var nRtn = db.NonQueryUseModel(
$"insert into {nameof(Person)}(name, birthday, addrid) values(@name, @birthday, @addrid)",
new Person
{
name = "tom1",
birthday = DateTime.Now,
addrid = 123,
},
//ignore properties
new[] { "id" });
Scalar:
DbContext db = new MSSqlDBContext();
var sRtn = db.ScalarUseModel(
$"select count(id) from {nameof(Person)} where name=@name", new
{
name = "tom1"
}, null);
Query:
DbContext db = new MSSqlDBContext();
var qRtn = db.QueryUseModel<Person>(
$"select name, birthday, addrid from {nameof(Person)} where name=@name", new
{
name = "tom1"
}, null,
//ignore properties for return type
new[] { "id" });
Execute Sql and Cache
var expiry = new QueryCacheExpiryPolicy(TimeSpan.FromSeconds(3));
//Cache
var val = db.ScalarCacheUseModel<Person>(
$"select count(*) from {nameof(Person)} where name=@name",
new { name = name }, null, expiry);
var expiry = new QueryCacheExpiryPolicy(TimeSpan.FromSeconds(3), true);
//Cache
var val = db.QueryCacheUseModel<Person, Person>(
"select * from {nameof(Person)} where name=@name",
new { name = name }, null, null, expiry);
clear cache
//clear cache for sql (table: Person, cache type: query)
db.QueryCacheRemoveUseModel<Person>("select * from {nameof(Person)} where name=@name", new { name = name }, null);
//clears the cache for the specified type:query (table: Person)
EFHelper.Services.Cache.QueryRemove<Person>();
//clears the cache for the specified table: Person
EFHelper.Services.Cache.Remove<Person>();
IQueryable(linq) cache
DbContext db = new MSSqlDBContext(); var person = db.Set<Person>(); //ListCache(FirstOrDefaultCache / CountCache / LongCountCache / Cache(others)) // parameter 1: table name // parameter 2: expiry time(Here is set to not expired) IReadOnlyList<Person> list = person.Where(l => l.name == "tom1").ListCache(nameof(Person), null); //Same as above var list0 = person.Where(l => l.name == "tom1").ListCache<Person, Person>(null);
//set cache expiry var list1 = person.Where(l => l.name == "tom2") .ListCache(nameof(Person), new QueryCacheExpiryPolicy(TimeSpan.FromMinutes(15))); //15min //Same as above var list11 = person.Where(l => l.name == "tom2") .ListCache<Person, Person>(TimeSpan.FromMinutes(15)); //15min var list2 = person.Where(l => l.name == "tom3") .ListCache<Person, Person>(DateTime.Parse("2018-1-1")); //DateTime
clear cache
//clear cache for IQueryable (table: Person, cache type: List)
person.Where(l => l.name == "tom1").ListCacheRemove<Person>();
//clears the cache for the specified type:List (table: Person)
EFHelper.Services.Cache.ListRemove<Person>();
//clears the cache for the specified table: Person
EFHelper.Services.Cache.Remove<Person>();
Sql config to json file
config file: Person.json
{
"sqls": {
"GetList": {
//"sql": "select * from [Person] where name=@name",
"sql": "select * from ##tname where name=@name", //##tname => Table Name
"type": "query"
},
"Count": {
"sql": "select count(*) from ##tname",
"type": "scalar"
},
"UpdatePerson": {
"sql": "update ##tname set birthday=@birthday,addrid=@addrid where name=@name",
"type": "nonquery"
},
"AddPerson": {
"sql": "insert into ##tname(name, birthday, addrid) values(@name, @birthday, @addrid) ",
"type": "nonquery"
},
"DeletePerson": {
"sql": "delete from ##tname where name=@name",
"type": "nonquery"
}
}
}
Load config files:
EFHelper.Services.SqlConfigMgr.Config.LoadDirectory(Directory.GetCurrentDirectory()); //load from directory
//EFHelper.Services.SqlConfigMgr.Config.LoadFile(Directory.GetCurrentDirectory() + "/Person.json"); //load from file
use:
public class PersonBLL
{
string _name = "tom";
DBConfigTable tinfo;
public PersonBLL(DbContext db)
{
tinfo = db.GetConfigTable<Person>();
}
public IReadOnlyList<Person> GetList()
{
return tinfo.GetExecutor().QueryUseModel<Person>(
//Model => SqlParams
new { name = _name, id = 123 },
//ignore properties for Model
new[] { "id" },
//ignore properties for return type
new[] { "name" });
}
public int Count()
{
var exc = tinfo.GetExecutor();
var rtn = exc.ScalarUseModel(new { name = _name }, null);
return (int)typeof(int).ChangeValueType(rtn);
}
public int AddPerson()
{
return tinfo.GetExecutor().NonQueryUseModel(new Person
{
addrid = 1,
birthday = DateTime.Now,
name = _name,
}, null);
}
public int UpdatePerson(int? addrid = null)
{
var exc = tinfo.GetExecutor();
return exc.NonQueryUseModel(new { name = _name, birthday = DateTime.Now, addrid = addrid }, null);
}
public int DeletePerson()
{
return tinfo.GetExecutor().NonQueryUseModel(new
{
name = _name
}, null);
}
}
etc.