title: How do I store a List<> as a field in a database? - C# (SQL Server Compact Edition)
body: <p>I have an object whose fields I would like to store in a database. I will be using SQL Server Compact Edition (with Visual C# Express 2010). For the record, I'm fairly new to programming with databases (and databases in general). This program will be used every day to read emails, process the orders inside them, store them, and access them when necessary to help with completing the orders. The list of orders is going to become much to large to store in a List, write to a file, create the List from a file, etc. The problem is that each order contains a list of the items purchased. I am aware that I can serialize the list in binary or XML, and use that data as the field. However, this prevents me from searching/selecting based on that list. For instance, if I wanted to find an order based on what items are in it, or see how many times a particular item has been purchased. Since the list will be of arbitrary size, I can't just create a bunch of fields and fill only the ones I need (which in my opinion, is a bad idea anyway). </p>
  
  <p>While writing this I realized a mistake. If I serialize the list again, I could compare the serialized data to find the same list again (though, this assumes that the same data is serialized the same way each time). However, I'm still prevented from finding any particular item. </p>
  
  <p>So, is there any way to store the list of items, in a fixed number of fields (preferably 1) and still be able to search its contents with a query (I will most likely be using LINQ)?</p>
  
  <p>Edit:
  To address what I've gotten so far: first, thanks! I'm starting to piece together what I have to do, but I'm not quite there. The consensus seems to be to have a table for each set of items. Does that mean I'd be creating thousands of tables each month?</p>
  
  <p>After re-reading my question I realize I have to be more clear. As the order comes in, I parse the data and store it in an Order object, which consists of the customer's information, and the list of items. Here is a simplified version of what I'm trying to store:</p>
  
  <pre><code>class Order{
      private DateTime date;
      private String orderNumber;
      private String buyerName;
      private List&lt;Item&gt; items;
      private String address;
  }
  
  class Item{
      private String itemCode;
      private String description;
      private int quantity;
  }
  </code></pre>
  
  <p>So would I have to create a new table for each List I create, or am I missing something?</p>