Display joined table in CSHTML

Question

I am currently starting with a new tool called Webmatrix for creating websites based on CSHTML. Now I have some trouble with starting and cant find any help on other pages: I create Database call

var db = Database.Open("MyDB");
var selectQueryString = "SELECT * FROM a JOIN b ON a.id = b.id";

a and b both have the field "name" but I cant find any way to separate. I already tried

@foreach(var row in db.Query(selectQueryString)){
            <tr>
                <td>@row.id</td>
                <td>@row.a.name</td>
                <td>@row.b.name</td>
            </tr>
}

As I know it from MySQL in PHP. But that doesnt work. Can anybody help me with this (I guess) very simple question?

You are using ASP.Net and Razor... *Please don't use raw SQL anymore*. Just add an Entity framework edmx model. The models produced will allow access to the foreign key values in a strongly typed way. — iCollect.it Ltd, Aug 26 '15 at 10:23
@TrueBlueAussie for getting small amount of data from billions of records raw sql is more efficient. Please correct me if I am wrong. — Imad, Aug 26 '15 at 10:25
@ImadoddinIbnAlauddin: For getting small amounts of data... it will make no difference. EF is relatively efficient for the calls and the conversion overhead will not be noticed on a typical web page. The development benefits far outweigh any fears of it being slow :) (P.S. how did you jump from a simple WebMatrix site to billions of records? They are displaying the entire table so that would not be the case here) — iCollect.it Ltd, Aug 26 '15 at 10:26
@TrueBlueAussie As much as I know, `dbContext.Table1.Where()` will get all data from `Table1` and then filter out based on expression. So why should I get all data? Why can't just filter out it using raw sql? — Imad, Aug 26 '15 at 10:28
@ImadoddinIbnAlauddin: As there is no `where` clause in this example (it is the entire table), this is a pointless argument. Recommending *not to use* a very powerful strongly-typed data layer, because it might be slow for "billions of records" is very poor advice. Stop scaring people off using EF :) — iCollect.it Ltd, Aug 26 '15 at 10:29
@TrueBlueAussie I don't think so, because my table has around 3 million records and I noticed the different. On client with 512 kbps your page will be dead. I am not scaring anyone, I used EF in my projects and I loved it. it just depends on your business — Imad, Aug 26 '15 at 10:32
@ImadoddinIbnAlauddin: We use multi-million record tables with EF all the time and any speed problems are typically down to bad indexing. You do not show 3 million records at once, so again I say your argument is pointless except for very specific cases and not applicable to this question. — iCollect.it Ltd, Aug 26 '15 at 10:34
@TrueBlueAussie can you give me some example how to start in your way? — antoshkaa, Aug 26 '15 at 10:35
Read up on creating a "database first" model with Entity Framework. Your code becomes a very simply LINQ to SQL query on the database context like: `model = context.a` and the loop becomes `foreach (var row in Model)` and the items become simply `@row.id`, `@row.name` & `@row.b.name` as you almost have now. if you can provide a better example of your tables I will mock up a step-by-step example. — iCollect.it Ltd, Aug 26 '15 at 10:38
One important Question: Do you use Visual Studio for development? WebMatrix is not a "new" product and has nowhere near the features Visual Studio has. Even the free version of VS will do. — iCollect.it Ltd, Aug 26 '15 at 10:43

score 1 · Accepted Answer · edited May 23 '17 at 11:58

Specify the columns you require rather than using a wildcard asterisk.

Aside from resolving your issue this is also much better for performance.

var selectQueryString = "SELECT a.id, a.name as AName, b.name as BName FROM a JOIN b ON a.id = b.id";

Then you can do:

@foreach(var row in db.Query(selectQueryString)){
   <tr>
     <td>@row.id</td>
     <td>@row.AName</td>
     <td>@row.BName</td>
   </tr>
}

score 0 · Answer 2 · answered Aug 26 '15 at 10:56

0

Use entity framework, create separate class for data application layer, add Dbcontext, connection string put to web.config, and if you want looping two entities on the same time, you need something like this ->

var results = from ek in dbContext.tbl_EKTransfer
              where //...(what you want)
              select ek.ServisID
var results = from t in dbContext.tbl_Transfer
              where // ...(what you want)
              select t.ServisID;

results = results.Union(results2);

foreach (var result in results)
{

}

Or join second table.

answered Aug 26 '15 at 10:56

Pavel

21
2

You do not join tables like this in EF! Join them in the database (as you should) then they will be available as child properties. This is a very poor example. – iCollect.it Ltd Aug 26 '15 at 10:58
@TrueBlueAussie this very poor question, I dont know any information about entities, maybe one of them is aggregate of other, maybe it composition, therefore I think, need some information about mvc pattern for topic starter. – Pavel Aug 26 '15 at 11:11
@TrueBlueAussie most probably author has a problem with architecture. – Pavel Aug 26 '15 at 11:13
The question appears to be a simple 2-table join. Their problem is in the way they are referencing the fields using `db.Query()`. An EF solution would be a lot more work to setup, but the result will be simpler than even the code you have shown. No unions. One select. One foreach. – iCollect.it Ltd Aug 26 '15 at 11:14
@TrueBlueAussie yes you are right, if we can join those tables. – Pavel Aug 26 '15 at 11:36

Display joined table in CSHTML

2 Answers2