As the LINQ user responded, it is not directly possible.
However using Aline's suggestion to use an ORM I found the package "Dapper" and "Dapper.Contrib", which are extremely simple and do not require many configurations. To map the objects is great, even if you do not like using an ORM, since it allows you to use your queries in the traditional way.
Example usage:
public class Cidade
{
public int Id { get; set; }
public string Nome { get; set; }
public string Uf { get; set; }
public int Ativo { get; set; }
}
And here is where we map the result of any Query (example).
List<Cidade> listCidades;
Cidade model = new Cidade { Nome = "São Paulo" };
using (MySqlConnection con = new MySqlConnection(Config.GetConString()))
{
con.Open();
var cidades = con.Query<Cidade>
(
"SELECT * FROM cidades WHERE nome LIKE @nome",
new
{
nome = "%" + model.Nome + "%"
}
);
listCidades = cidades.ToList<Cidade>();
con.Close();
}
For simpler queries to insert, update, delete, you can use Dapper extensions.
T Get<T>(id);
IEnumerable<T> GetAll<T>();
int Insert<T>(T obj);
int Insert<T>(Enumerable<T> list);
bool Update<T>(T obj);
bool Update<T>(Enumerable<T> list);
bool Delete<T>(T obj);
bool Delete<T>(Enumerable<T> list);
bool DeleteAll<T>();
In this example I used MySQL and Dapper worked perfectly.
You can also use the Repository pattern to group these queries into their proper classes and save generic operations using a generic repository.
public abstract class RepositoryBase<T> where T : class
{
protected MySqlConnection con;
protected int timeout;
public RepositoryBase(MySqlConnection con, int timeout = 10)
{
this.con = con;
this.timeout = timeout;
}
public long Insert(T model)
{
return con.Insert<T>(model);
}
public bool Update(T model)
{
return con.Update<T>(model);
}
public bool Delete(T model)
{
return con.Delete<T>(model);
}
}
And for the child classes.
public class CidadesRepository : RepositoryBase<Cidade>
{
public CidadesRepository(MySqlConnection con, int timeout = 10) : base(con, timeout) {}
public List<Cidade> Search(Cidade model)
{
var cidades = con.Query<Cidade>
(
"SELECT * FROM cidades WHERE nome LIKE @nome OR uf LIKE @uf",
new
{
nome = "%" + model.Nome + "%",
uf = "%" + model.Uf + "%"
}
);
return cidades.ToList<Cidade>();
}
}
Note that after installing the packages via Nu-Get, you need to include the references in the code and in the project.
using Dapper;
using Dapper.Contrib.Extensions;
Dapper: link