Join Identity Table and Data Table MVC5 EF6

Question

My data table is a list of articles written by authenticated users.

On the index page I would like to show a list of articles as well as the DisplayName of the author for each. The problem is the DisplayName is stored in my applicationDBContext identity table and my Data is stored in a DBContext (seperate databases on my server). The unique USERID key is on both tables, iD on the Identity table and Author ID on the Data table. I have tried different tihngs, giving different errors, the most common one being

"The specified LINQ expression contains references to queries that are associated with different contexts"

The code that generates that error is as follows:

var arts = (from a in db.Articles
                        join uid in iDdb.Users
                            on a.AuthorID equals uid.Id
                        select new ArticleVM
                   {
                       ID = a.ID,
                       ArticleTitle = a.ArticleTitle,
                       ArticleBody = a.ArticleBody.Substring(0, 200),
                       ArticleCategory = a.ArticleCategory,
                       DateCreated = a.DateCreated,
                       AuthorID = uid.DisplayName
                   }).AsEnumerable();

db.Articles in the Data tableon the data database and IDdb.Users is the User table (on the identity database). I can write an SQL query to generate what I need, but how do I put that data into a ViewModel, example, this SQL query will give me what I need:

var query = @"select artDB.*, usrDB.DisplayName from Article as artDB inner
                            join [IGTSIAppUsersTest1].dbo.AspNetUsers as usrDB on 
                            artDB.AuthorID = usrDB.Id order by artDB.ID Desc";
            var arts = db.Database.ExecuteSqlCommand(query);

Can anyone suggest a way to do this that is still somewhat efficient? I found one way to make it work but it requires a ridiculous amount of code and really hurts performance.

Probably the easiest thing to do is to create a view in the "articles" database to pull in your user info, and then join that view to your articles table (possibly via another view). Then you would only need to query the articles DbContext. — Tieson T., Jan 18 '18 at 05:15
I am trying to do that, but it gives me a message "Unable to create a constant value of type 'Project.ViewModels.UserDisplayNameVM'. Only primitive types or enumeration types are supported in this context.". Still trying to find a solution — dave317, Jan 18 '18 at 05:23
By "view", I mean a [database view](https://learn.microsoft.com/en-us/sql/relational-databases/views/views), not a view model. — Tieson T., Jan 18 '18 at 05:27

score 0 · Answer 1 · answered Jan 26 '18 at 01:12

I found a way to do this. It works as long as you are only viewing data and not changing data. I found the answer here

    string query = (@"select a.*, usr.DisplayName as 'DisplayName' from article a
join identitytablename.dbo.AspNetUsers usr on
datatable.Id = a.authorid");
            IEnumerable<ArticleIndexVM> results = db.Database.SqlQuery<IndexViewModel>(query);

Works well as long as your ViemModel Types and names match up with the results of the query table names and types

score -2 · Answer 2 · answered Jan 18 '18 at 06:24

-2

One of the approach here, you can use the same context for Users and Articles. As following link below.

Use the same database for both my custom entities and identity context?

answered Jan 18 '18 at 06:24

ocrenaka

192
2
8

I know i can do this easily. I do not want to setup my web app this way. – dave317 Jan 18 '18 at 16:15
In that case, you can use SQL View with EF6 ref: https://stackoverflow.com/questions/7461265/how-to-use-views-in-code-first-entity-framework but the limitation is one view can use for one join query only. – ocrenaka Jan 19 '18 at 07:11

Join Identity Table and Data Table MVC5 EF6

2 Answers2