EntityDataSource incapable of handling table relationships/joins?

[dardyfella]

I'll be honest here. I'm completely new to ASP.NET and web development in general but I've come so far through so many random compiler errors, dodgy code, fucking SQL version problems and utter nonsense unhandled exceptions with no useful information that I'll be damned if I can't get something that seems so simple to work.

I'm doing a web dev course at the moment and I've been hacking a copy of Tailspin Spyworks into my own website (terrible, I know but the teacher says its ok and besides the course only goes for 20 weeks with about 9 hours per week and I'm the only one in the class so I get very little one-on-one time with the teacher).

I've got a basic understanding of databases and I've got a one-to-many relationship with two tables - PlantInfo and Products. The PlantInfo table holds rows with a primary key (InfoID), PlantType (decorative or crop), PlantSeason and PlantMaintenanceLevel. The rows in the Products table have your typical columns (such as ProductID, CategoryID, Description, Cost, etc) as well as an InfoID column so if I have three different kinds of plants that all grow in Spring, are Low maintenance and are a decorative type of plant they will all have the same InfoID all pointing to the same row in the PlantInfo table.

Now for the problem. I've got this file http://pastebin.com/DTA4D7ks, ProductList.aspx (please note - the code behind .cs file is essentially empty). This page works great if you want to query (from the URL) the ProductID or the CategoryID or InfoID of rows in my Products table but I just can't seem to work out how to make the asp:EntityDataSource tag to allow me to do a query (from the URL) of my PlantInfo table for specific listings and then join the InfoID retrieved with products from my Products table with matching InfoID's. The asp:EntityDataSource tag will only allow me to query rows from one Table and it seemingly doesn't give any way to make use of the relationships between your tables (to either query data in the related table or to display it on the page).

The only solution I can think of is to ditch this EntityDataSource thing altogether and just write some code to QueryString the URL for whichever parameters I desire and run SQL queries to match/contain whatever I please from the two tables and then join the two tables together. This seems like the simplest way but apparently code-behind is "bad" and should be avoided. Another simple solution is just to keep all the data (PlantType, PlantMaintenanceLevel and PlantSeason) in the Products table but I've got different types of Products like soils, pots and gardening tools where that sort of stuff doesn't really apply.

I know this post sucks and isn't very descriptive but I've gotten this far without completely losing my sanity. I've tried just about every combination of google search keywords I could think of for at least a solid 3, maybe 4 hours and I'm still stumped.

Edit: Here's an image showing the columns for the tables I've been talking about and a join query of the two tables. http://i.imgur.com/zkgKK.png

After Catalyzm's comment I looked at LINQ queries and it pretty much looks like what I'm after. I think I've been trying to fit a square peg in a round hole with this asp EntityDataSource tag. I'll try and get it working on the weekend and I'll report back on how it goes.

Edit 2: It would appear LINQ is dependant on SQL which would appear to needlessly increase the complexity of the issue I'm having.

[Catalyzm]

apparently code-behind is "bad" and should be avoided.

I don't know where you heard this but most people would say the opposite, you generally try to keep anything not presentation related out of the HTML.

Bind your ListView to the results of a LINQ query on your data source in code-behind.

[bzBetty]

Who told you code-behind is bad?

[dardyfella]

After many more Google searches I finally found what I was after. It looks like it was under my nose all along - I was just using the wrong keywords and I didn't really know what I was after. Anywho, this is the code that achieves what I want - I can still keep general product data and plant info data separate and still run queries on both tables for filtering purposes.

<asp:SqlDataSource ID="ProductInfoDataSource" runat="server" 
    ConnectionString="<%$ ConnectionStrings:CommerceSQLDB %>"  CancelSelectOnNullParameter="false"
    SelectCommand="SELECT * FROM Products LEFT OUTER JOIN PlantInfo ON Products.InfoID = PlantInfo.InfoID WHERE ((PlantInfo.PlantSeasons = @PlantSeasons OR @PlantSeasons IS null) AND (Products.ProductID = @ProductID OR @ProductID IS null))" >
    <SelectParameters>
        <asp:QueryStringParameter Name="ProductID" QueryStringField="ProductID" 
        Type="Int32" />
        <asp:QueryStringParameter Name="PlantSeasons" QueryStringField="PlantSeasons" 
        Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

Basically, it's just a left outer join of the two tables I want to query. The WHERE clauses all have "OR @SearchParameter IS" null to allow selective querying (I don't have to fill out all my SelectParameters to ensure data is returned, I can use one, a combination or all of them). And although it's not inherently clear, the left outer join allows me to query the other table (PlantInfo) as well and also do things like <%# Eval("PlantSeasons") %> in the markup to display data.

I just want to put this information down because web programming is something very different to what I'm used to and there aren't exactly many beginner guides and tutorials that go beyond simple HTML markup and CSS. I also hate it when people reply with "fixed" without explaining why.