Best place to start

Question

User1390052142 posted

Hello! I'm looking for a bit of advice here.

I am creating a maintenance website for tracking vehicles in the shop, upcoming maintenance for them, parts for those vehicles, etc. I am using asp.net core MVC and Entity Framework with a SQL database. I have no experience with databases before. I have used a plug in that created all the Models for each table in the database along with all of the properties. It also created the context model. So from my understanding, what I need to do from here is basically figure out how to get information from the database, how to manipulate it, and then also how to update the info in the database.

My question is where do I start? Breaking everything down it seems like the smallest part to start with would be to go through each of the different models 1 by 1 and create all of the queries for each model.
For instance, for the Manufacture model, I would create a query to retrieve all of the information, then I would create a query to modify the number of vehicles we own under that manufacture, then I would create a query to adjust the amount of money we have spent with that manufacture. This makes sense to me, but I don't know if I'm thinking about this in the correct way.
Also, if it is correct, I don't know where to put these queries. 

Answer 1

User541108374 posted

Hi,

which plugin are you talking about? Some extension in Visual Studio or Entity Framework Data Migrations tool to generate your database?

1 by 1 and create all of the queries for each model.

If you use Entity Framework or another ORM framework it'll generate the queries for you.

As you mentioned ASP.NET Core and Entity Framework already I suggest you go through these tutorials to quickly get up to speed with the technology and get a grasp of what's going on: https://docs.microsoft.com/en-us/aspnet/core/data/ef-mvc/intro.

Kris.

Answer 2

User-1838255255 posted

Hi ethancodes,

After reading your description and needs, i think you wanted is EF DB first, you could convert db to dbcontext, then operate it in linq. For more details and how to operate it, please check the following tutorials: 

Entity Framework 6.0 Introduction:

http://www.entityframeworktutorial.net/entityframework6/introduction.aspx 

Entity Framework Database First: 

https://msdn.microsoft.com/en-us/library/jj206878%28v=vs.113%29.aspx?f=255&MSPPError=-2147217396 

Best Regards,

Eric Du 

Answer 3

User1390052142 posted

We used EF Core Power Tools to reverse engineer the database. I will look at this tutorial. Thank you!

Also, I guess I'm confused when you say it will generate the queries for me. I see several Models, each with a partial class and properties, and then I see MaintenanceContext that inherits from DbContext, which has properties for each of the other models, which also has the below code in it, but I'm not entirely sure what this does. It seems to be taking the information for each property? But I don't see any queries anywhere.

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Area>(entity =>
            {
                entity.Property(e => e.LocationName)
                    .IsRequired()
                    .HasMaxLength(128);

                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(128);
            });

            modelBuilder.Entity<Asset>(entity =>
            {
                entity.Property(e => e.AssetTag)
                    .IsRequired()
                    .HasMaxLength(128);

                entity.Property(e => e.Description)
                    .IsRequired()
                    .HasMaxLength(128);

                entity.Property(e => e.SerialNum)
                    .IsRequired()
                    .HasMaxLength(50);

                entity.HasOne(d => d.AssetType)
                    .WithMany(p => p.Asset)
                    .HasForeignKey(d => d.AssetTypeId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Asset_Asset Type");

                entity.HasOne(d => d.Condition)
                    .WithMany(p => p.Asset)
                    .HasForeignKey(d => d.ConditionId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Asset_Condition");

                entity.HasOne(d => d.Manufacture)
                    .WithMany(p => p.Asset)
                    .HasForeignKey(d => d.ManufactureId)
                    .HasConstraintName("FK_Asset_Manufacture");

                entity.HasOne(d => d.Vendor)
                    .WithMany(p => p.Asset)
                    .HasForeignKey(d => d.VendorId)
                    .HasConstraintName("FK_Asset_Vendor");
            });

            modelBuilder.Entity<Asset_Type>(entity =>
            {
                entity.ToTable("Asset Type");

                entity.Property(e => e.Type)
                    .IsRequired()
                    .HasMaxLength(128);
            });

            modelBuilder.Entity<Comment>(entity =>
            {
                entity.Property(e => e.Entry).IsRequired();

                entity.Property(e => e.UserId)
                    .IsRequired()
                    .HasMaxLength(128);

                entity.HasOne(d => d.Ticket)
                    .WithMany(p => p.Comment)
                    .HasForeignKey(d => d.TicketId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Comment_Ticket");
            });

            modelBuilder.Entity<Condition>(entity =>
            {
                entity.Property(e => e.Condition1)
                    .IsRequired()
                    .HasColumnName("Condition")
                    .HasMaxLength(128);
            });

            modelBuilder.Entity<InventoryItem>(entity =>
            {
                entity.Property(e => e.Description).HasMaxLength(128);

                entity.Property(e => e.ItemNumber)
                    .IsRequired()
                    .HasMaxLength(128);

                entity.Property(e => e.LocationName).HasMaxLength(128);

                entity.Property(e => e.Url)
                    .HasColumnName("URL")
                    .HasMaxLength(128);

                entity.HasOne(d => d.Vendor)
                    .WithMany(p => p.InventoryItem)
                    .HasForeignKey(d => d.VendorId)
                    .HasConstraintName("FK_InventoryItem_Vendor");
            });

            modelBuilder.Entity<Manufacture>(entity =>
            {
                entity.Property(e => e.Name)
                    .IsRequired()
                    .HasMaxLength(128);
            });

            modelBuilder.Entity<Meter>(entity =>
            {
                entity.HasOne(d => d.Asset)
                    .WithMany(p => p.Meter)
                    .HasForeignKey(d => d.AssetId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Meter_Asset");

                entity.HasOne(d => d.MeterUnits)
                    .WithMany(p => p.Meter)
                    .HasForeignKey(d => d.MeterUnitsId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Meter_Meter Units");
            });

            modelBuilder.Entity<Meter_Readings>(entity =>
            {
                entity.ToTable("Meter Readings");

                entity.HasOne(d => d.Meter)
                    .WithMany(p => p.Meter_Readings)
                    .HasForeignKey(d => d.MeterId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_Meter Readings_Meter");
            });

            modelBuilder.Entity<Meter_Units>(entity =>
            {
                entity.ToTable("Meter Units");

                entity.Property(e => e.Type)
                    .IsRequired()
                    .HasMaxLength(128);
            });

            modelBuilder.Entity<PM_Schedules>(entity =>
            {
                entity.ToTable("PM Schedules");

                entity.Property(e => e.Description).IsRequired();

                entity.Property(e => e.PmInterval).HasMaxLength(128);

                entity.HasOne(d => d.Asset)
                    .WithMany(p => p.PM_Schedules)
                    .HasForeignKey(d => d.AssetId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_PM Schedules_Asset");
            });

            modelBuilder.Entity<Priority>(entity =>
            {
                entity.Property(e => e.PriorityType)
                    .IsRequired()
                    .HasMaxLength(128);
            });

            modelBuilder.Entity<Status>(entity =>
            {
                entity.Property(e => e.Status1)
                    .IsRequired()
                    .HasColumnName("Status")
                    .HasMaxLength(128);
            });

            modelBuilder.Entity<TechnicianNotes>(entity =>
            {
                entity.Property(e => e.Entry).IsRequired();

                entity.Property(e => e.UserId)
                    .IsRequired()
                    .HasMaxLength(128);

                entity.HasOne(d => d.Ticket)
                    .WithMany(p => p.TechnicianNotes)
                    .HasForeignKey(d => d.TicketId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_TechnicianNotes_Ticket");
            });

            modelBuilder.Entity<Ticket>(entity =>
            {
                entity.Property(e => e.AssignToId).HasDefaultValueSql("(newid())");

                entity.Property(e => e.AssignToName).HasMaxLength(128);

                entity.Property(e => e.DepartmentName).HasMaxLength(128);

                entity.Property(e => e.LocationName).HasMaxLength(128);

                entity.Property(e => e.RequestedById).HasDefaultValueSql("(newid())");

                entity.Property(e => e.RequestedByName).HasMaxLength(128);

                entity.HasOne(d => d.Area)
                    .WithMany(p => p.Ticket)
                    .HasForeignKey(d => d.AreaId)
                    .HasConstraintName("FK_Ticket_Area");

                entity.HasOne(d => d.AssociatedAsset)
                    .WithMany(p => p.Ticket)
                    .HasForeignKey(d => d.AssociatedAssetId)
                    .HasConstraintName("FK_Ticket_Asset");

                entity.HasOne(d => d.Priority)
                    .WithMany(p => p.Ticket)
                    .HasForeignKey(d => d.PriorityId)
                    .HasConstraintName("FK_Ticket_Priority");

                entity.HasOne(d => d.Status)
                    .WithMany(p => p.Ticket)
                    .HasForeignKey(d => d.StatusId)
                    .HasConstraintName("FK_Ticket_Status");

                entity.HasOne(d => d.TicketType)
                    .WithMany(p => p.Ticket)
                    .HasForeignKey(d => d.TicketTypeId)
                    .HasConstraintName("FK_Ticket_TicketType");
            });

            modelBuilder.Entity<TicketItemUsed>(entity =>
            {
                entity.HasOne(d => d.InventoryItem)
                    .WithMany(p => p.TicketItemUsed)
                    .HasForeignKey(d => d.InventoryItemId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_TicketItemUsed_InventoryItem");

                entity.HasOne(d => d.Ticket)
                    .WithMany(p => p.TicketItemUsed)
                    .HasForeignKey(d => d.TicketId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_TicketItemUsed_Ticket");
            });

            modelBuilder.Entity<TicketType>(entity =>
            {
                entity.Property(e => e.Type)
                    .IsRequired()
                    .HasMaxLength(128);
            });

            modelBuilder.Entity<Vendor>(entity =>
            {
                entity.Property(e => e.Name).HasMaxLength(128);
            });
        }

Answer 4

User475983607 posted

Go through a few Getting Started tutorials before moving forward so that you understand the basics.

Entity FRamework

https://msdn.microsoft.com/en-us/library/aa937723(v=vs.113).aspx

https://msdn.microsoft.com/en-us/library/ee712907(v=vs.113).aspx

Linq

https://msdn.microsoft.com/en-us/library/jj573936(v=vs.113).aspx

https://docs.microsoft.com/en-us/dotnet/csharp/programming-guide/concepts/linq/getting-started-with-linq