How to join in entity framework

Question

0.00/5 (No votes)

See more:

, +

Hello

I have 2 tables named "User" and "Letter". each User can have many letters, so the relationship is "one to many". I want when a User login, see all letters that he/she have received in the jqxGrid. So I need Json format for my resources data to fill jqxGrid. I have wrote the codes below, but it shows Firs letter for each User due to "FirstorDefult". so
1)How can I return multi records ?
2) How can I convert Them to Json format?

Thanks in Advance

What I have tried:

in .js file :

JavaScript

var source =
    {
        // fetching data to grid
        datatype: "json",
        datafields: [
                    { name: 'UserName', type: 'string' },
                    { name: 'Text', type: 'string' }
        ],
        localdata: response,
    };

    // Data Adapter for Generating data for Grid
    var dataAdapter = new $.jqx.dataAdapter(source, {
        downloadComplete: function (data, status, xhr) { },
        loadComplete: function (data) { },
        loadError: function (xhr, status, error) { }
    });

in Controller :

C#

public string ShowUser()
        {
            using (Test02Entities _Test02Entities = new Test02Entities())
            {
                Join _Join = new Join();
                Letter _Letter = new Letter();
                Users _Users = _Test02Entities.Users.Where(c => c.ID==6).SingleOrDefault();
                if (_Users != null)
                {
                     _Letter = _Test02Entities.Letter.Where(c => c.UserID == _Users.ID).FirstOrDefault();
                    _Join.Text = _Letter.Text;
                    _Join.UserName = _Users.UserName;
                }
                return JsonConvert.SerializeObject(_Join);
            }
        }

        public partial class Join
        {
            public string UserName { get; set; }
            public string Text { get; set; }
        }

Posted 20-Oct-16 18:54pm

Ali Majed HA

Updated 21-Oct-16 2:04am

Add a Solution

Comments

Ehsan Sajjad 21-Oct-16 2:20am

is it asp.net mvc?

Ali Majed HA 21-Oct-16 2:46am

it is web api

F-ES Sitecore 21-Oct-16 8:40am

Get a book on EF and go through it, or at least look at on-line tutorials. Linking entities is quite a basic aspect of EF and it is extremely wel documented.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Nathan Minier · Answer 1 · 2016-10-21T02:04:00

How this will work depends entirely on your Entity Framework approach.

If this is your database, and you're using Code First, the very best thing that you can do is define Navigation properties on your model classses. For instance:

C#

public class Users
{
   ... 
   public virtual ICollection<Letter> Letters { get; set;}
}

public class Letter
{
   ...
   [ForeignKey("User")]
   public int UserId { get; set; }

   [JsonIgnore]
   public virtual Users User { get; set; }
}

public string ShowUser(int id)
{
   using (Test02Entities _Test02Entities = new Test02Entities())
   {
      var user = _Test02Entities.Users.Find(id);
                
      return JsonConvert.SerializeObject(_Join);
   }
}

The JsonIgnore attribute will avoid the circular reference serialization issue. You can change this based on need, but that's outside the scope of a quick answer.

This pattern leverages the natural relational nature of data in a relational database to provide automatic joins, and is really one of the best ways to use Entity Framework (IMO).

As a side note, avoid using SingleOrDefault() if at all possible in EF as that will enumerate the entire table. FirstOrDefault() will stop enumeration when the condition is met, and Find() will pull directly by row key.

There is a primer for Navigation properties at:
Entity Framework Relationships & Navigation Properties[^].

If you're using Database First, you can do exactly the same thing by adding Associations to the edmx file. This approach is not as firmly in my realm, but there is a solid reference at:
Entity Framework Relationships - EF Designer[^]