LINQ Modeling with Automatic Data Retrieval via EntityRef
I'm using LINQ to model my database, but I'm writing it by hand (Steve Sanderson recommends this in his ASP.NET MVC book). I need to know what happens when you create the EntityRef and how it is referenced. If I create my queries manually (without using LINQ) but use LINQ to model it and I only return the id of something and then reference the actual table column using EntityRef in the View, does it do a join or does it re-query database for this bit of information?
To clear everything if I have this in my repository:
public IEnumerable<MyTable> ListMyTable(int? myColumnVar)
{
string query = "SELECT * FROM MyTable WHERE MyColumn = {0}";
return this.ExecuteQuery<MyTable>(query, myColumnVar);
}
and then in my controller i do this:
IEnumerable<MyTable> mytables = _contractsControlService.ListMyTable(1);
return View(mytables);
then in my opinion i do things like
<%=tbl.Ref.MyColumn %>
I am referencing something set by the LINQ model, but is not actually in the output of the table. How do I get this data?
To clarify the situation, we are using systems that require maximum speed, so LINQ-to-SQL is too slow for us, so we use direct queries in our repository. I wouldn't mind using this EntityRef business if I knew what's going on below.
source to share
Most likely, you used the Relation Object constructor to create an entity class for the Ref object and the relationship between MyTable and the Ref object. You can also do this manually by creating the appropriate entity classes and using attributes to map the classes to the database schema. You can read the details at How to Configure Object Classes Using a Code Editor (LINQ to SQL) on MSDN.
You will find some attributes in your generated (or handwritten) code:
[Table(Name="dbo.Ref")]
public partial class Ref : INotifyPropertyChanging, INotifyPropertyChanged
and
public partial class MyTable: INotifyPropertyChanging, INotifyPropertyChanged
{
[Association(Name="Ref_MyTable", Storage="_Ref", ThisKey="RefId",
OtherKey="Id", IsForeignKey=true)]
public Ref Ref
{
get
...
Entity classes, when combined with attributes, enable the Linq-to-Sql framework to retrieve object classes directly from the database.
If you want to control the SQL generated by Linq-to-Sql you can assign a property DataContext.Log
:
context.Log = Console.Out;
In your example, going from MyTable to Ref is likely generating SQL along these lines:
SELECT Id, Field1, Field2, Field3
FROM Ref
WHERE Id = @RefId
source to share