CODEWITHSUNDEEP
c#sql
Eldar
Eldar

Reputation: 164

Dynamic SQL query (string based) on a C# List

I was trying to find a way to query a C# list as a data base using a string sql query, for example :

List<Customer> customers = new List<Customer>(){
    new Customer(){Name = "joe", Age = 20},
    new Customer(){Name = "john", Age = 25}
};
string query = @"Select * from customers";
List<Tuple> tuples = EntityDB.query(customers, query);
Console.Writeline(tuples[0][0]); //prints "joe"

I got several requirements that needs to be met using this feature,

I dont have a SQL sever on, I dont have additional mdf DB, its just this list of objects of customers, is there a way to do it?

my motivation for this is the ability to extract specific info from this list to a 2D table and output it to excel, but the row and column filtering is not predefined, and multi-row operations are needed, users will be able to specify the exact info they want to extract like with SQL query.

Upvotes: 1

Views: 2908

Answers (4)

craftworkgames
craftworkgames

Reputation: 9957

Rather than trying to simulate a database in code, have you considered using a lightweight database to solve your problem? Something like SQLite comes to mind. Normally it writes a file to the disk, but actually supports being loaded in-memory too.

According to the documentation an in memory SQLite database does not need to write a file to disk and exists in memory while you have the connection open. Once the connection is closed it ceases to exist.

http://www.sqlite.org/inmemorydb.html

Once you have the connection open you can query the data exactly like you would with any other database using regular SQL strings, or if you prefer you could query it using LINQ with NHibernate or some other supported ORM.

SQLite is designed especially for times when you need the power of SQL but without the overhead of setting up a full database server. In fact, if your using Firefox as your browser, you're using SQLite right now ;)

Upvotes: 1

Basic
Basic

Reputation: 26766

Have you considered just building up the Linq in the same way you'd build up the string?

Something like

var customers = new List<Customer> { … };
IQueryable<Customer> results = customers.AsQueryable();


if(!string.IsNullOrEmpty(CustomerName))
    results = results.Where(x => x.Name = CustomerName);

if(CustomerMaxAge != 0)
    results = results.Where(x => x.Age <= CustomerMaxAge);

//etc....

return results.ToList();

By using an IQueryable you can defer the in-memory stuff until you've finished adding criteria.

Upvotes: 1

Hogan
Hogan

Reputation: 70523

Maybe this is what you want?

 List<Tuple<string,int>> tuples = customers.Select(x => Tuple.Create(x.Name,x.Age)).ToList();

Upvotes: 0

Kirk Broadhurst
Kirk Broadhurst

Reputation: 28708

LINQ allows you to execute query-like commands against a variety of datasources - a database, an XML file, application objects. You can select items from a list using LINQ very easily:

items.Select(c => c.Customer)

When this is done against in memory objects you are using LINQ-to-Objects.

I'd prefer this over writing a SQL-to-objects framework. That sounds like a real maintenance nightmare.

Upvotes: 4

Related Questions