Relational Database SQL Query in Asp.NET Core

c# entity-framework-core

Question

public async Task<List<Note>>ShowAssigned()
{
 return await _context.Notes
           .Where(x => x.List.OwnerId != x.OwnerId)
           .ToListAsync()
}

I get no syntax εrrors, but it seems you can't access attributes from related Data in this way.

Basically the goal is: A user creates a List, then some Notes for this List. Then he should be able to assign one of that Notes to another User. When that other User logs on, he should be able to see that new Note that was assigned to him.

Can anyone help me out with this?

public class List
{
    public Guid ListId { get; set; }
    public string OwnerId { get; set; }
    public List<Note> Notes { get; set; }
}



public class Note
{
    public Guid ID { get; set; }
    public string OwnerId { get; set; }
    [ForeignKey("ListId")]
    public Guid ListId { get; set; }
    public List List { get; set; }
}

And the context class:

public DbSet<Note> Notes { get; set; }
public DbSet<List> Lists { get; set; }

When i try to access Data the same way in a view like that:

 @model List<Project.Models.Note>
 @foreach (var item in Model)
{
    if (item.List.OwnerId == item.OwnerId)

i get this error when running the web app (no syntax errors): NullReferenceException: Object reference not set to an instance of an object

1
0
9/6/2018 4:22:04 PM

Accepted Answer

So I found the answer to my problem, in some parts with the help of TanvirArjel (but i basically did it differently)

 public async Task<List<Note>> GetAssignedItemsAsync(ApplicationUser user)
    {

        var lists = await _context.Lists.Include(l => l.Notes).Where(x => x.OwnerId != user.Id).ToListAsync();

        var notesListe = new List<Note>();

        foreach (List l in lists)
        {
            foreach (Note n in l.Notes)
            {
                if (n.OwnerId == user.Id)
                {
                    notesListe.Add(n);
                }
            }
        }
        return  notesListe;

    }
0
9/5/2018 8:41:08 AM

Popular Answer

First write your model classes as follows:

public class List
{
    public Guid ListId { get; set; }
    public string OwnerId { get; set; }

    public virtual List<Note> Notes { get; set; }
}

public class Note
{
    public Guid ID { get; set; }
    public string OwnerId { get; set; }

    [ForeignKey("List")] // Not ListId, its List
    public Guid ListId { get; set; }

    public virtual List List { get; set; }
}

If your project is on ASP.NET Core < 2.1

Then write your query as follows:

await _context.Notes.Include(n => n.List).ToListAsync()

If your project is on ASP.NET Core >= 2.1

Then in the ConfigureServices() method in Startup class:

services.AddDbContext<ApplicationDbContext>(options =>
   options.UseLazyLoadingProxies().UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

Don't forget to install appropriate version of Microsoft.EntityFrameworkCore.Proxies nuget package because UseLazyLoadingProxies() resides in this package.

Then write your query as follows:

await _context.Notes.ToListAsync()


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow