C# Querying With LINQ Querying the BirdWatcher Data Sightings of Endangered Birds

Kirsty Pollock
Kirsty Pollock
14,255 Points


I have many many, many, many years of complex SQL, many , many of C#, and even a fair bit of LINQ ... and this is bending my brain.

I think I have worked out why - it's the "document" style data structure. I am not used to it - normalised relational data tables with unique keys and foreign keys have always been a breeze for me to deal with with LINQ. It just all fits. But this doesn't seem to. And I am not sure if there are slight bugs, or just I can't wrap my head round how one is intended to use LINQ with this style of data.

eg. here, the Sighting has a Bird property (not ideal compared to just a key) - but it is never populated on the data load making any joins much more horrible to achieve than they need be, IMO. (it also fails to populate Family - that IS a bug, but not one anyone would notice in this course).

If you are a beginner and find this hard - take courage, it is a mite confusing even to those who have spent the past 15 years wrangling data with C#. (and maybe try some SQL to see if the relational style of stuff makes more sense to you).

If you have the same sort of background as myself, and have managed the "brain flip" - any hints would be appreciated!

Oziel Perez
Oziel Perez
61,319 Points

Agreed! I'm not a newb when it comes to SQL or OOP but I don't consider myself pro either. However, the querying in this course is grueling. If you are just getting started in programming, don't try to remember everything in this course. It's just not possible. The most important bits are the basics (select, order by, groups, where, and the aggregates). Look up joins or any other complex queries if the need arises. I don't often have to use queries like that in simple databases or websites that use them.

2 Answers

Carling Kirk
Carling Kirk
Treehouse Guest Teacher

Hi Kirsty!

It sounds like you are used to using a provider like LINQ to Entities or LINQ to SQL. This course uses "vanilla" LINQ to Objects on IEnumerable types. The goal is to get familiar with all the methods and to learn how delegates work.

It is a lot easier to use LINQ when you have a normalized data schema. Many of the joins we perform in this course are unnecessary when using normalized data - but it's good to know how to use them for when the need arises!

We'll use LINQ to Entities in an upcoming course on Entity Framework, and hopefully that will be more familiar for you!

I'm somewhat familiar with functional programming concepts like map and filter and these linq functions are pretty analogous but I'm not learning anything linq specific in this course. I feel like I'm learning how to do this by rote rather than understanding anything that i'm doing. As a result I could follow along in this video but it was almost impossible for me to try out the solution before I was shown it.

Luis Marsano
Luis Marsano
21,388 Points

Honestly, the query was needlessly complex: brains don't need to bend for this. A "filter, collect, transform" approach would have sufficed, been succinct, clearer, efficient.

  1. Filter out unwanted data.
  2. Group it.
  3. Transform the result.
.Where(b => !( b.ConservationStatus == "LeastConcern"
            || b.ConservationStatus == "NearThreatened"
.GroupBy( b => b.ConservationStatus
        , (c, bs) => new { ConservationStatus = c
                         , Sightings = bs.SelectMany(b => b.Sightings).Count()

Here, we filter out unwanted conservation statuses, group by conservation status, then transform each of those groups to the result we want. Though we could look at intermediate results as we build this, I find the mess of data less helpful than clear expectations and a simple awareness of resulting types. Filters (Where) leave types alone. Groupings (GroupBy) map a sequence to a sequence of such sequences. Transforms (Select or extra parameter of GroupBy) map a sequence to any type of sequence you want.

Functional programming should be clear and mostly self-explanatory. A programmer who comes up with complicated code for an idea that is not that complicated probably missed a simpler solution and should challenge themselves to find it.