none
Coding multiple WHERE clauses in a LINQ to DataSet query RRS feed

  • Question

  • Ok, I've been working on this one for a while before asking for assistance. I have 4 Oracle based SQL queries which follow one another to deliver the requested data. I'm working in .NET and I think (I hope) I could use LINQ to DataSet to pass the first query along to the second, which passes to the third, and finally to the fourth. Where I'm running into a headache is with passing from the first to the second. I can get the first query into a DataSet through normal ODP.NET and C# queries.

    Here is step 2

    SELECT eid,
    num_1
    ,
    MIN
    (cdts) keep (dense_rank FIRST ORDER BY eid) first_creation,
    cpers
    ,
    curent
    ,
    ag_id
    ,
    beat
    ,
    group_priority
    ,
    disp_date
    ,
    MIN
    (disp_time) keep (dense_rank FIRST ORDER BY eid) first_call,
    curent_16
    ,
    MIN
    (ad_sec) keep (dense_rank FIRST ORDER BY eid) first_entry,
    MIN
    (ds_sec) keep (dense_rank FIRST ORDER BY eid) first_dispatched,
    MIN
    (ar_sec) keep (dense_rank FIRST ORDER BY eid) first_arrival,
    MIN
    (csec) keep (dense_rank FIRST ORDER BY unid) fastest_unit,
    MIN
    (hold_sec) keep (dense_rank FIRST ORDER BY eid) fastest_dispatch,
    MIN
    (drive_sec) keep (dense_rank FIRST ORDER BY unid) fastest_enroute,
    MIN
    (resp_sec) keep (dense_rank FIRST ORDER BY unid) fastest_arrival,
    unid FROM JC5A_STEP1 GROUP BY eid
    ,
    num_1
    ,
    cpers
    ,
    curent
    ,
    ag_id
    ,
    beat
    ,
    group_priority
    ,
    disp_date
    ,
    curent_16
    ,
    unid HAVING cpers
    <> 0 AND curent = 'T' AND curent_16 ='T' ORDER BY eid;

    I've been trying to write a LINQ to DataSet which will populate this into a DataSet of its own. This is what I have so far and I know I'm way off

    var query2 = (from row in query1.AsEnumerable() where row.Field<int32>("cpers") != 0 && row.Field<string>("curent") == "T" && row.Field<string>("curent_16") == "T" order by row.Field<Int32>("eid") select new { eid = row.Field<Int32>("eid"), ... }).Min(x => x.cdts); 

    I omitted all of the non-min fields and the one which is included for brevity. So, my question is, can I write a LINQ to DataSet query which will encompass what I have in the SELECT statement, and if so, where am I off my rails? If not, will I need to develop all of this in PL/SQL functions and call them from the application as Stored Procedures?

    If anyone needs, I will forward along the first query, just didn't want to make this so long everyone falls asleep reading it.

    Thanks to everyone for any assistance,

    T.

     

    Tuesday, May 3, 2011 5:09 PM

Answers

  • Hello Tony,

     

    Welcome to the MSDN Forum.

    According to your description, I'm not sure you can use LINQ to DataSet but I recommand you to use Entity Framework. For example:

    var query1 = (from s in context.Times

                                 where s.start_date >= time && s.end_time <= end

                                 select s.end_time).ToList();

     

                    var query2 = (from c in query

                                  select c).ToList();

                    foreach (var i in query2)

                    {

                        Console.WriteLine(i);

                        Console.ReadLine();

                    }

    In the query2, you can query from the result collection of query1. More information about using EF, please read this:

    http://blogs.msdn.com/b/slange/archive/2011/04/01/using-oracle-and-visual-studio-together.aspx

    If you want to use PL/SQL functions, you can repost your issue in SQL and PL/SQL  of Oracle Forum.

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 5, 2011 2:43 AM
    Moderator

All replies

  • Hello Tony,

     

    Welcome to the MSDN Forum.

    According to your description, I'm not sure you can use LINQ to DataSet but I recommand you to use Entity Framework. For example:

    var query1 = (from s in context.Times

                                 where s.start_date >= time && s.end_time <= end

                                 select s.end_time).ToList();

     

                    var query2 = (from c in query

                                  select c).ToList();

                    foreach (var i in query2)

                    {

                        Console.WriteLine(i);

                        Console.ReadLine();

                    }

    In the query2, you can query from the result collection of query1. More information about using EF, please read this:

    http://blogs.msdn.com/b/slange/archive/2011/04/01/using-oracle-and-visual-studio-together.aspx

    If you want to use PL/SQL functions, you can repost your issue in SQL and PL/SQL  of Oracle Forum.

    I hope this can help you.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, May 5, 2011 2:43 AM
    Moderator
  • Jackie,

    Thanks for getting back to me. I will look at EF and see how I can leverage that to make it work.

    Thanks much, will keep you posted on any results.

    Thursday, May 5, 2011 1:29 PM
  • What about using a query to supply a set of data to a where value? E.g. In sql:

    select * from payrolldetails
    where bankBranchID in 
    (select ID from  bankBranch where location = "south")
    Bare in mind that multiple IDs will be returned.  How do I code that using Linq? 


    Tuesday, July 15, 2014 8:29 PM
  • I ended up fixing this like this:

    from p in payrolldetails
    where (from b in bankBranch
    where location == "south"
    select new { b.ID }).Equals(p.bankBranchID)
    select u;
    And that worked for me very nicely.

    Wednesday, September 24, 2014 4:51 PM