Entity Framework Core extension library
PM> Install-Package EFCoreExtend
PM> Install-Package EFCoreExtend.Redis
PM> Install-Package EFCoreExtend.Lua
blog:http://www.cnblogs.com/skig/p/EFCoreExtend.html
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" });
DbContext db = new MSSqlDBContext();
var sRtn = db.ScalarUseModel(
$"select count(id) from {nameof(Person)} where name=@name", new
{
name = "tom1"
}, null);
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" });
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>();
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>();
config file: Person.lua
cfg.table("Person", function(tb)
tb.sqls.Get = function () --return "select * from Person where name=@name"; --return "select * from " .. tb.name .." where name=@name"; return "select * from " .. tb.name .." where name=@name", "query"; end tb.sqls.Count = function () return "select count(*) from Person where name=@name", "scalar"; end tb.sqls.Add = function () return "insert into Person(name, birthday, addrid) values(@name, @birthday, @addrid)", "nonquery"; end tb.sqls.Update = function () return "update Person set addrid=@addrid where name=@name" end tb.sqls.Delete = function () return "delete from Person where name=@name" end
end);
Load config files:
EFHelper.ServiceBuilder.AddLuaSqlDefault().BuildServices(); //add lua services
var luasql = EFHelper.Services.GetLuaSqlMgr(); //get lua service
luasql.LoadFile(Directory.GetCurrentDirectory() + "/Datas/Lua/Person.lua"); //load lua config file
//luasql.LoadDirectory(Directory.GetCurrentDirectory() + "/Datas/Lua/Cache"); // load files
use:
public class LuaPersonBLL
{
string name = "skig";
protected readonly LuaDBConfigTable config;
public LuaPersonBLL(DbContext db)
{
config = db.GetLuaConfigTable<Person>();
}
public IReadOnlyList<Person> Get()
{
return config.GetLuaExecutor().QueryUseModel<Person>(new
{
name = name,
});
}
public int Count()
{
return config.GetLuaExecutor().ScalarUseModel<int>(new
{
name = name,
});
}
public int Add()
{
return config.GetLuaExecutor().NonQueryUseModel(new Person
{
name = name,
addrid = 123,
birthday = DateTime.Now,
}, "id");
}
public int Update(int? addrid = 345)
{
return config.GetLuaExecutor().NonQueryUseModel(new
{
name = name,
addrid = addrid,
});
}
public int Delete()
{
return config.GetLuaExecutor().NonQueryUseModel(new
{
name = name
});
}
}
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.