1

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,

  • it needs to support dynamic SQL queries (string queries), because I will need to extract specific rows from this list, and the filtering is not predefined. (linq is not helpful here)
  • it needs to be able to support functionalities on several rows (like Average, Count...) exactly like SQL.
  • it needs to support extraction of specified columns exactly like SQL (select column1, column2...)

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.

Improve this question
5
  • What exactly do you mean by 'treat objects as DB'? Commented Aug 25, 2012 at 22:21
  • 1
    You CAN'T use sql string to query a List<> collection, it's a nonsense. Why on Earth would you want to do that? Commented Aug 25, 2012 at 22:25
  • the idea is that this list is a small DB, I need to extract specific values from it exactly like querying a DB. so far linq sounds like a solution, but the queries I will execute will be dynamic, linq doesnt support it. Commented Aug 25, 2012 at 22:45
  • Off-topic suggestion: I would perhaps store a DateOfBirth rather than Age, because Age is dependent on when your code executes, and how long your objects might stay in memory. What happens when your code runs around New Year's Eve / New Year's Day…? DateOfBirth is independent from all that, and the correct Age can be easily derived from it at any time. Commented Aug 25, 2012 at 22:50
  • I see what you mean, but using customers.Select(c => new object[] { c.Name, c.Age }).ToArray(); is hard coded not dynamic... it doesnt what I was looking for. Commented Aug 25, 2012 at 22:54

4 Answers 4

Reset to default
4

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I know linq, the problem is that I have dynamic queries, strings, as far as I know, Linq cant parse my queries, I have to do it myself, is there another way, not type based?
If you have a specific dynamic query that you need to implement, you should include that in your question. You question is 'how do I implement this query using LINQ', not 'how do I treat my list as a database'.
1

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 ;)

Improve this answer

1 Comment

hi, thanks, close enough to what I was looking for, thanks a lot :), cheers!
1

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.

Improve this answer

2 Comments

its not enough, what I really looking for is a sql behavior, as the returning tuple needs to be truncated to the selected coloums... thanks
@Kaleldar Understood. You could use reflection to generate an anonymous type with the appropriate properties or failing that, write a method which takes a lambda specifying the properties you want and returns a dictionary (or similar). It's not ideal and you'd still need a switch in there when calling your method but it would work. (Something like Dictionary<T1,T2> = GetTuples<Customer, T1,T2>(results, x=>x.Name, y=>y.Age))
0

Maybe this is what you want?

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.