CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
JustAPleb
JustAPleb

Reputation: 309

C# LINQ to Entities - Select specified Data from two tables, populate existing Model Classes

I have a database and am using C# LINQ to Entities using Query Syntax.

Each DB table has it's own Entity Framework generated model class, the DB tables and model classes are named Release and Version (there are others but this is my immediate problem).

I'm trying to load specific data into these model classes based on a query.

Essentially I want every Release column & row, along with its associated Version records populated in the Navigation Property Release.Versions.

However, two crucial points here:

1) I only want certain 'Version' properties to be populated from the Query (I only need some of the data for use in an Overview page - I can't see the point of dragging back all the data (which could be quite sizeable) if I am not using it).

2) I want the query to populate the already existing Model classes of Release and Version. (Release.Versions Navigation property being populated with the Version records with cherry picked data from point 1 above)

All this I could do in a long winded way with Stored Procedures - no issue, but thought EF Linq would be quicker. Ha. Ha. Haaaa.

So:

Release.ID            <---- Primary Key
Release.Col_1         <---- Other Properties (or columns)
Release.Col_2
Release.Col_3
Release.Versions      <---- Navigation Property to collection of Version records for each Release.

Version.ID            <---- Primary Key
Version.Release_ID    <---- Foreign Key to Release Table
Version.Col_1         <---- Many properties, just want first 4 properties obtained from DB.
Version.Col_2
Version.Col_3
Version.Col_4
Version.Col_5
Version.Col_x...

I just can't get the syntax right.

This is what I have (I've tried many other variants but nothing as simple as I remember it being):

var query = (from r in context.Releases
             join v in context.Versions on r.ID equals v.Release_ID
             select r).ToList();

returnedRecords= new ObservableCollection<Model.Release>(query);

I seem to remember having to use the select new Release { x,y,z } and then a nested new Version { x, y, z} - but cannot for the life of me remember how to do it, or how to get it to populate the Navigation property Versions within the Release class.

Maybe using Lambda syntax???????

EDIT:

OK, it appears I cannot do what I thought I could do. So, I'm not going to use Query Syntax as most of the tutorials and documentation I can find are in Method Syntax. Plus I think Include only works with Method syntax, so seems a bit pointless using Query Syntax.

Answer marked as correct is how I loaded the Navigation Properties.

Upvotes: 1

Views: 1294

Answers (2)

Mohammad Al Safra
Mohammad Al Safra

Reputation: 114

You can simply load 'foreign' records for your table by using Include method.

var x = context.Releases.Include("Versions").ToList();

Upvotes: 3

Marcus H&#246;glund
Marcus H&#246;glund

Reputation: 16811

If you want the query to return the model class Release with a nested Version class, you can do it like this:

var query = (from r in context.Releases

             join v in context.Versions on r.ID equals v.Release_ID

             select new Release 
                        { 
                            releaseColumn1 = r.releaseColumn1, 
                            releaseColumn12 = r.releaseColumn2,
                            releaseVersion = new Version
                                                {
                                                    versionColumn1 = v.versionColumn1,
                                                    versionColumn2 = v.versionColumn2
                                                }
                        }).ToList();

Upvotes: 2

Related Questions