none
Need help adding HAVING COUNT and ORDER BY to LINQ statement

    Question

  • Hello,

    I am new both to StreamInsight and LINQ and I am having difficulty adding either an ORDER BY or HAVING COUNT to my code. I originally took a sample from the Internet and modified the MSSQL tables/columns to my database. First this is what I have at the beginning, the declaraction of the variable source.

                        //create StreamInsight application container
                        Application app = server.CreateApplication("app");
    
                        //define the (point event) source by creating an enumerable from the GetEvents operation
                        var source = app.DefineEnumerable<ServerEvent>(() => GetEvents()).
                            ToPointStreamable<ServerEvent, ServerEvent>(
                                e => PointEvent.CreateInsert<ServerEvent>(e.CREATED_DATE, e), 
                                AdvanceTimeSettings.StrictlyIncreasingStartTime);
    
    Then this is the LINQ code I am having trouble adding an ORDER BY and/or HAVING COUNT(*) > 10

    var query5 = from ev in source where ev.GROUP_ID == 18 || ev.GROUP_ID == 2 // orderby ev.GROUP_ID descending group ev by new { ev.USER_ID, ev.GROUP_ID } into levelgroup2 from win in levelgroup2.TumblingWindow(TimeSpan.FromMinutes(2)) select new EventSummary2 { EventCount2 = win.Count(), EventMessage1 = levelgroup2.Key.USER_ID, EventMessage2 = levelgroup2.Key.GROUP_ID }; //create observer as sink and write results to console var sink5 = app.DefineObserver(() => Observer.Create<EventSummary2>(x => Console.WriteLine("USER_ID: " + x.EventMessage1 + " GROUP_ID: " + x.EventMessage2.ToString() + " / Count: " + x.EventCount2.ToString()))); //bind the query to the sink using (IDisposable proc = query5.Bind<EventSummary2>(sink5).Run("MyProcess")) { Console.WriteLine("Press [Enter] to close the application."); Console.ReadLine(); }

    This above code seems to work as it is returning result when I run it. Now I would like to add/try two more things:

    1- I am trying to ORDER BY the result to the Console Window but I don't know what I am supposed to write and where. Can I even write "orderby" statement in this LINQ code, or a I completely off here in the sense that orderby would not be valid here?

    2- Second how do I add HAVING COUNT(*) > 10 for example?

    I would appreciate any help.

    Thank you.

    Thursday, February 14, 2013 2:47 PM

Answers

  • 1. What are you trying to accomplish by ordering the events? Remember that this is streaming data and not static data like in a database.

    2. Add a second step to your query5 like this:

    var query5Step2 = from e in query5
    where e.EventCount2 > 10
    select e;
    
    // TODO: bind query5Step2 to sink5

    • Marked as answer by sergez Monday, February 18, 2013 3:45 PM
    Thursday, February 14, 2013 4:16 PM

All replies

  • 1. What are you trying to accomplish by ordering the events? Remember that this is streaming data and not static data like in a database.

    2. Add a second step to your query5 like this:

    var query5Step2 = from e in query5
    where e.EventCount2 > 10
    select e;
    
    // TODO: bind query5Step2 to sink5

    • Marked as answer by sergez Monday, February 18, 2013 3:45 PM
    Thursday, February 14, 2013 4:16 PM
  • Hello,

    2. I did write it and it works, thanks for the solution!

    1. Ok I understand, but I would like to do the orderby on the sink output console window. Can I do that?

    Thanks for your help.

    Friday, February 15, 2013 2:15 PM
  • 2. No problem.

    1. Yes and no. You are dealing with streaming data so it's ordered by the event starttime. So if you are displaying the events in a sink output console, they are going to appear in the order that they arrived at the sink. While I don't recommend this, you could store the events in your sink as they arrive into a List<ServerEvent> and then flush this list of events to your output console window in the Observer's OnCompleted method. You could also batch up results and flush periodically. Depending on how you are displaying the data, maybe your UI should handle ordering the events as they arrive?

    Friday, February 15, 2013 2:50 PM
  • 1. Ok I see. For the time being I don't necessarily need an order by so I'll continue learning and experimenting and see how things go.

    Thanks a lot for your help.

    Monday, February 18, 2013 3:45 PM