CODEWITHSUNDEEP
c#.netlinq
sterix24
sterix24

Reputation: 2400

How do I create a new table of results using LINQ?

To sum up my situation: I have a collection of Events that have a date property. I need to create a new table that basically lists the count of events for a particular date.

So assuming I had a series of records like so:

enter image description here

What I'd like to do is to be able to create a new table from this using LINQ that'd select the date and then a count of the events on that date like so:

enter image description here

I'm not really sure where to begin in creating this statement. I was wondering if someone could point me in the right direction?

Thanks

EDIT: My apologies - it seems I was a little confused! I'm actually using NHibernate and I have a statement that returns a collection of all the events for all dates.

I realise now I can just iterate over the collection and build a new collection of classes that contains the data I need :-)

Upvotes: 0

Views: 2346

Answers (2)

Bruno Yamasaki
Bruno Yamasaki

Reputation: 26

Well, the only thing you need to do with LINQ is to GroupBy date.

Here´s one example:

    /// <summary>
    /// Your entity
    /// </summary>
    public class Events
    {
        public int EventID { get; private set; }
        public string Name { get; set; }
        public DateTime EventDate { get; set; }
    }

    public class Program
    {
        static void Main(string[] args)
        {
            // Query all events
            IEnumerable<Events> events = null; 

            // Group them by Date
            events
                .GroupBy(e => e.EventDate)
                .Select(g => new
                {
                    Date = g.Key,
                    Count = g.Count()
                });
        }
    }

Upvotes: 1

Łukasz Zwierko
Łukasz Zwierko

Reputation: 629

LINQ is only for quering data sets / collections, you can't create tables or any other database objects with it. If you need to create database tables dynamically sooner or later you'll end up sending sql commands directly to DB, and if you use linq-to-sql or EF you will not be able to easily query these tables (using generated schema code). What I would do, I'd write stored procedures, one for creating result table, second for inserting data to it (it it's a lot of data, use batch inserts instead) and third one for reading thee data (if you need it). In EF you can map a stored procedure to a method, so it'll be bit better structured than "CREATE TABLE " + tableName +"...." etc directly in code. Still you will need to pass the table name as parameter, but that's kind of obvious if you want to work on what will actually become schema-less database. Other options that you might want to consider: 1. going for nosql 2. keeping all results in a signle table and adding a column where you keep table name 3. serializing results to some sort of XML and inserting whole result XML as an entry to key-value table, where the key would be table name.

It all comes down to how much data would you store in the table and if you need to update it.

Upvotes: 1

Related Questions