C#•4w ago

I require some help understanding MVC and Database loading/updating

Hi, I'm very new to MVC and new to ASP.NET CORE, So these questions may be all over the place. I do have an understanding of C# & Object-Oriented programming. I'm trying to make a web application that has two tables, a Users table and a UserPhoneNumbers table, for this app I am allowing a single User to have Multiple UserPhoneNumber attached to it. I understand that I'll need a Model for each. Here are my two example models

c#
public class User
{
    [Key, Required]
    public string AccountId { get; set; }

    [StringLength(255), Required]
    public string Email { get; set; }
    
    [StringLength(255), Required]
    public string FirstName { get; set; }
    
    [StringLength(255), Required]
    public string LastName { get; set; }
    
    public ICollection<UserPhoneNumber> UserPhoneNumbers { get; set; }
}

public class UserPhoneNumber
{
    [Key, StringLength(32), Required]
    public string? UserPhoneNumberId { get; set; }
    
    [StringLength(32), Required]
    public string? PhoneNumber { get; set; }
    
    public User User { get; set; }

}

c#
public class User
{
    [Key, Required]
    public string AccountId { get; set; }

    [StringLength(255), Required]
    public string Email { get; set; }
    
    [StringLength(255), Required]
    public string FirstName { get; set; }
    
    [StringLength(255), Required]
    public string LastName { get; set; }
    
    public ICollection<UserPhoneNumber> UserPhoneNumbers { get; set; }
}

public class UserPhoneNumber
{
    [Key, StringLength(32), Required]
    public string? UserPhoneNumberId { get; set; }
    
    [StringLength(32), Required]
    public string? PhoneNumber { get; set; }
    
    public User User { get; set; }

}

Lets presume I have a working database and can perform basic CRUD operations. Here are my questions:

38 Replies

JasonOP•4w ago

How do models work? I have these two models, I add the migrations & update the database. It creates these two tables Users and UserPhoneNumbers what next? How do I add or load the data that is already in the table? Lets for a moment say I have the following user: User jason = new User(AccountId = 1, Email = "[email protected]", FirstName = "Jason", LastName="Person"); My presumption was to create a method inside of the Models.User class and inside that method check to see if the database already contains an entry matching AccountId or Email. However upon trying to research the topic I got lost down a rabbit hole and found multiple different articles that say I shouldn't have anything other then the properties that relate to the table inside the Models class & that I should create either a "Service" or "Repository" class that handles the CRUD operations. I'm just looking for confirmation on that being correct. I guess what I'm asking is: Should the Models.User class ONLY contain the properties corresponding to the table? Should I be using a Repository/Service class for my CRUD operations?* Whats the difference between Repository class and a Service class? How on earth do database relationships work? In the two example classes I provided above, In User I have a ICollection<UserPhoneNumber> UserPhoneNumbers and in UserPhoneNumber User User Lets say I already have a user loaded from the database User jason = LoadUserJasonFromTheAboveQuestion(); How would i get the phone number, would it be as simple as UserPhoneNumber numbers = jason.UserPhoneNumbers[0]? I know that last question was a bit vague, but my understanding of database relationships is very little.

Angius•4w ago

1. You use the DbContext you also must've made 2. With LINQ, since that's how EF does it Find:

var user = await context.Users
    .Where(u => u.Id == id)
    .FirstOrDefaultAsync();

var user = await context.Users
    .Where(u => u.Id == id)
    .FirstOrDefaultAsync();

Create:

context.Users.Add(user);
await context.SaveChangesAsync();

context.Users.Add(user);
await context.SaveChangesAsync();

Update:

var rows = await context.Users
    .Where(u => u.Id == id)
    .ExecuteUpdateAsync(s =>
        s.SetProperty(u => u.Name, newName));
// OR
var user = await context.Users
    .Where(u => u.Id == id)
    .FirstOrDefaultAsync();
user.Name = newName;
await context.SaveChangesAsync();

var rows = await context.Users
    .Where(u => u.Id == id)
    .ExecuteUpdateAsync(s =>
        s.SetProperty(u => u.Name, newName));
// OR
var user = await context.Users
    .Where(u => u.Id == id)
    .FirstOrDefaultAsync();
user.Name = newName;
await context.SaveChangesAsync();

Delete:

var rows = await context.Users
    .Where(u => u.Id == id)
    .ExecuteDeleteAsync();
// OR
var user = await context.Users
    .Where(u => u.Id == id)
    .FirstOrDefaultAsync();
context.Users.Remove(user);
await context.SaveChangesAsync();

var rows = await context.Users
    .Where(u => u.Id == id)
    .ExecuteDeleteAsync();
// OR
var user = await context.Users
    .Where(u => u.Id == id)
    .FirstOrDefaultAsync();
context.Users.Remove(user);
await context.SaveChangesAsync();

Getting the related data can be done two ways 1. By including everything from that relationship

var user = await context.Users
    .Where(u => u.Id == id)
    .Include(u => u.PhoneNumbers)
    .FirstOrDefaultAsync();

var user = await context.Users
    .Where(u => u.Id == id)
    .Include(u => u.PhoneNumbers)
    .FirstOrDefaultAsync();

2. By selecting only what you need into a DTO

sealed record UserWithNumbers(int Id, string Name, string[] PhoneNumbers);

var user = await context.Users
    .Where(u => u.Id == id)
    .Select(u => new UserWithNumbers(
        u.Id,
        $"{u.FirstName} {u.LastName}",
        u.PhoneNumbers.Select(pn => pn.Number)
    ))
    .FirstOrDefaultAsync();

sealed record UserWithNumbers(int Id, string Name, string[] PhoneNumbers);

var user = await context.Users
    .Where(u => u.Id == id)
    .Select(u => new UserWithNumbers(
        u.Id,
        $"{u.FirstName} {u.LastName}",
        u.PhoneNumbers.Select(pn => pn.Number)
    ))
    .FirstOrDefaultAsync();

The latter is the recommended way In general, database models should never leave the application boundary. All incoming and outgoing requests should be their own models, DTOs DTO — Data Transfer Object Sometimes also called POCOs, Plain Old C# Objects

JasonOP•4w ago

Okay, Thanks for that info, however I'm still stuck on these three little sub questions: Should the Models.User class ONLY contain the properties corresponding to the table? Should I be using a Repository/Service class for my CRUD operations? Whats the difference between Repository class and a Service class?

Angius•4w ago

1. Yes 2. Some people believe so, some people don't. One thing for sure, you should not be using a generic repository if you decide to use them, but rather specific repositories for each model 3. The difference is kinda blurry, but in general repositories contain mostly very generic code (Create, Delete, ListAll, etc) while services are a bit more specific (DeleteExpiredItems, ListAllSortedByDate, FindCheapestItem, etc)

JasonOP•4w ago

Lets see if i got this, its a little generic, but would this be a correct file structure

APP

-Models
--User
--UserPhoneNumbers

-Services
--Helper

-Interfaces
--IRepository

-Repositories
--UserRepository
--UserPhoneNumbersRespository

APP

-Models
--User
--UserPhoneNumbers

-Services
--Helper

-Interfaces
--IRepository

-Repositories
--UserRepository
--UserPhoneNumbersRespository

Where Models are litterally just the tables of the database, Where Services are generic classes, Where Interfaces contains a interface that would layout the basic CRUD requirements for my Repository classes Where Repositories would implement the specific methods required for each model, i.e the CRUD methods described in the IRepository

Angius•4w ago

This would be a very classic, enterprise-like architecture, yes

JasonOP•4w ago

Early in my testing I created a Services.Database class, Given what you have told me, I'm on the correct idea, but my implementation of the EntryExists and GetEntry methods are wrong https://gist.github.com/Nashy1232/e12d68e5d21ed73ffc9b7556efef13c5

Gist

0fYSC1UMBA.txt

GitHub Gist: instantly share code, notes, and snippets.

Angius•4w ago

Yeah, that seems extremely overcomplicated I'm stunned that you wrote all of that before googling "ef how check if exist" lmao

JasonOP•4w ago

I wrote that very much with the help of google, but for the love of me I can't remember what I googled. I just remember my goal was to make the most generic / reusable method to get an entry from a database

Angius•4w ago

EF already provides the most generic/reusable methods

JasonOP•4w ago

So, i could compact that entire class down into just this? https://gist.github.com/Nashy1232/fc8cec276582df95c3278449a697c007

Gist

rhJippuoi2.txt

GitHub Gist: instantly share code, notes, and snippets.

JasonOP•4w ago

by adding replacing EntryExists with:

c#
            var entry = await context.Set<T>()
                .Where(e => EF.Property<string>(e, columnName) == identifier)
                .FirstOrDefaultAsync<T>();
            
            if (entry == null)
                return identifier;

c#
            var entry = await context.Set<T>()
                .Where(e => EF.Property<string>(e, columnName) == identifier)
                .FirstOrDefaultAsync<T>();
            
            if (entry == null)
                return identifier;

Angius•4w ago

Or just don't have the EntryExists method at all, and simply use

var exists = await context.Things.AnyAsync(t => t.Prop == foo);

var exists = await context.Things.AnyAsync(t => t.Prop == foo);

Your GenerateUniqueIdentifier also seems like a ghetto version of a GUID

JasonOP•4w ago

GUID?

Angius•4w ago

Globally Unique IDentifier

MODiX•4w ago

Angius

REPL Result: Success

Guid.NewGuid()

Guid.NewGuid()

Result: Guid

046d6365-c308-4bfa-9683-4bbbd8314f74

046d6365-c308-4bfa-9683-4bbbd8314f74

Compile: 312.750ms | Execution: 22.066ms | React with ❌ to remove this embed.

JasonOP•4w ago

huh, I guess technically it is, I wanted an ID that was 4 sections of 4 characters serperated by a hash, for example 1KA4-AD24-5412-DASD

Angius•4w ago

Why?

JasonOP•4w ago

Good question...

Angius•4w ago

IMO you could even just use sequential IDs

JasonOP•4w ago

I guess originally I didn't want them just auto increment index eventually I planend on having an API where you can provide account ID & a token but honestly, i'd be better of creating an API key and token that can be revoked and generate the API key independent of the account ID

Angius•4w ago

That would be ideal, yeah Well, OAuth2 would be ideal, but that's it's own can of worms

JasonOP•4w ago

At the moment, my goal is to familiarise myself with the MVC style and the basics before even attempting to deal with authentication and the likes I think I'm going to keep the GenerateUniqueIdentifier method, but I think it needs to be reworked as I do want each account to have a unique account id that isn't auto-incremented

Angius•4w ago

Or just let the database generate a GUID But sure, whatever works for you

JasonOP•4w ago

huh, how?

Angius•4w ago

[DatabaseGenerated(DatabaseGeneratedOption.Identity)] on the PK should work

JasonOP•4w ago

            entity.HasKey(e => e.CustomerId)
                .HasName("PRIMARY")
                .HasComputedColumnSql("SELECT UUID()");

            entity.HasKey(e => e.CustomerId)
                .HasName("PRIMARY")
                .HasComputedColumnSql("SELECT UUID()");

right? i like your way better

Angius•4w ago

Or what you sent, yes Assuming the database you're using supports GUID/UUIDs

JasonOP•4w ago

mariaDB, I'd have to double check that It doens't look like it does support it

Angius•4w ago

You could always just set the key in the constructor and make the setter private

JasonOP•4w ago

but it looks like i can override the OnModelCreating method to have it work in the same way

Angius•4w ago

public abstract class BaseModel
{
    public BaseModel()
    {
        Id = Guid.NewGuid();
    }

    public Guid Id { get; private set; }
}

public abstract class BaseModel
{
    public BaseModel()
    {
        Id = Guid.NewGuid();
    }

    public Guid Id { get; private set; }
}

and have every model inherit it Or yeah, could override that

JasonOP•4w ago

I know with a GUID the chances are minuscule, but wouldn't that setter have to check to make sure that its actually unique? I've got an issue. I'm running the web server and database in docker containers. When I'm running the container my DbContext has to be server=mariadb however when I'm trying to add migrations and update the database from the IDE i have to change my DbContext to another connection string that uses server=localhost is there any way i can fix this?

Angius•4w ago

GUID is basically guaranteed to be unique I have no experience with Docker, so can't help you there, I'm afraid Besides maybe store the connection string in the config file, and simply load different config files in Docker vs local Appsettings.[environment].json will be loaded only in a given [environment]

JasonOP•4w ago

ooh, that would work i think i went back and tested this, it does work with the DatabaseGeneratedOption can confirm, it did work!

Angius•4w ago

Nice

JasonOP•4w ago

Again, Thanks for this help, you've helped me understand this way more then 2 days of googling trial & error

JasonOP•4w ago

Hey, so, I think i've got the hang of modifying the database, could you double check this and tell me if i'm on the correct track https://gist.github.com/Nashy1232/060b49b4c5b0e6f774da96ad608601c4

Gist

DdE2RhxDuU.txt

GitHub Gist: instantly share code, notes, and snippets.

Gaming

Programming

I require some help understanding MVC and Database loading/updating