Get 1 record from 2 tables ASP.NET

Question

I am still an ASP.NET amateur and I've been working on an application that needs to calculate the hours an employee has worked if no special events have come up e.g the employee has been sick, I have 2 tables in my database, 1 with the employees. and a second table which holds the events. the events table is filled through a calendar and holds info like dates and who made the event.

My situation: When the user clicks on an employee's detail page. I want the corresponding record of the employee, and the events he made. So I am assuming that I am looking for a join with linq.

An employee can make more than 1 event, let's say an employee needs to work overtime 3 days this month. then on the detail page, it should select the employee from the employee table and the 3 events from the events table.

Update

Thanks to Vladimir's help, a whole lot of errors are gone and the query works. Though it does not completely work as expected yet. it currently returns 1 employee and 1 event. While the employee that I am testing with, should have 4 events returned.

This is my Context

namespace hrmTool.Models
{
public class MedewerkerMeldingContext : DbContext
{
    public MedewerkerMeldingContext() : base("name=temphrmEntities") { }
    public DbSet<medewerker> medewerker { get; set; }
    public DbSet<medewerker_melding> medewerker_melding { get; set; }
}
}

My current viewModel

namespace hrmTool.Models
{
public class MedewerkerMeldingViewModel
{
    //Medewerker tabel
    public int ID { get; set; }
    public string roepnaam { get; set; }
    public string voorvoegsel { get; set; }
    public string achternaam { get; set; }
    public string tussenvoegsel { get; set; }
    public string meisjesnaam { get; set; }
    public Nullable<System.DateTime> datum_in_dienst { get; set; }
    public Nullable<System.DateTime> datum_uit_dienst { get; set; }
    public int aantal_km_woon_werk { get; set; }
    public bool maandag { get; set; }
    public Nullable<System.TimeSpan> ma_van { get; set; }
    public Nullable<System.TimeSpan> ma_tot { get; set; }
    public bool dinsdag { get; set; }
    public Nullable<System.TimeSpan> di_van { get; set; }
    public Nullable<System.TimeSpan> di_tot { get; set; }
    public bool woensdag { get; set; }
    public Nullable<System.TimeSpan> wo_van { get; set; }
    public Nullable<System.TimeSpan> wo_tot { get; set; }
    public bool donderdag { get; set; }
    public Nullable<System.TimeSpan> do_van { get; set; }
    public Nullable<System.TimeSpan> do_tot { get; set; }
    public bool vrijdag { get; set; }
    public Nullable<System.TimeSpan> vr_van { get; set; }
    public Nullable<System.TimeSpan> vr_tot { get; set; }
    public bool zaterdag { get; set; }
    public Nullable<System.TimeSpan> za_van { get; set; }
    public Nullable<System.TimeSpan> za_tot { get; set; }
    public bool zondag { get; set; }
    public Nullable<System.TimeSpan> zo_van { get; set; }
    public Nullable<System.TimeSpan> zo_tot { get; set; }

    //Medewerker_Melding combi tabel
    public int medewerkerID { get; set; }
    public int meldingID { get; set; }
    public System.DateTime datum_van { get; set; }
    public Nullable<System.DateTime> datum_tot { get; set; }
    public int MM_ID { get; set; }
    public virtual ICollection<medewerker_melding> medewerker_melding { get; set; }

    public virtual medewerker medewerker { get; set; }
}
}

My current query

using (var context = new MedewerkerMeldingContext())
        {



            var medewerkers = context.medewerker;
            var medewerker_meldings = context.medewerker_melding;

            var testQuery = from m in medewerkers
                            join mm in medewerker_meldings on m.ID equals mm.medewerkerID
                            where m.ID == id
                            select new MedewerkerMeldingViewModel
                            {
                                ID = m.ID,
                                roepnaam = m.roepnaam,
                                voorvoegsel = m.voorvoegsel,
                                achternaam = m.achternaam,
                                tussenvoegsel = m.tussenvoegsel,
                                meisjesnaam = m.meisjesnaam,
                                datum_in_dienst = m.datum_in_dienst,
                                datum_uit_dienst = m.datum_uit_dienst,
                                aantal_km_woon_werk = m.aantal_km_woon_werk,
                                maandag = m.maandag,
                                ma_van = m.ma_van,
                                ma_tot = m.ma_tot,
                                dinsdag = m.dinsdag,
                                di_van = m.di_van,
                                di_tot = m.di_tot,
                                woensdag = m.woensdag,
                                wo_van = m.wo_van,
                                wo_tot = m.wo_tot,
                                donderdag = m.donderdag,
                                do_van = m.do_van,
                                do_tot = m.do_tot,
                                vrijdag = m.vrijdag,
                                vr_van = m.vr_van,
                                vr_tot = m.vr_tot,
                                zaterdag = m.zaterdag,
                                za_van = m.za_van,
                                za_tot = m.za_tot,
                                zondag = m.zondag,
                                zo_van = m.zo_van,
                                zo_tot = m.zo_tot,
                                medewerkerID = mm.medewerkerID,
                                meldingID = mm.meldingID,
                                datum_van = mm.datum_van,
                                datum_tot = mm.datum_tot,
                                MM_ID = mm.ID
                            };

            var getQueryResult = testQuery.FirstOrDefault();

            Debug.WriteLine("Debug testQuery" + testQuery);
            Debug.WriteLine("Debug getQueryResult:   "+ getQueryResult);

            if (id == null)
            {
                return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
            }

            if (testQuery == null)
            {
                return HttpNotFound();
            }
            return View(getQueryResult);
        }

Returns: 1 instance of employee and only 1 event

Expected return: 1 instance of employee, 4 events

Answer 1

In your context DbContext is missing - so Linq to Entity can not find corresponding implementation of the query. And also DbContext operates with the DbSets - so try:

public class MedewerkerMeldingContext : DbContext
{
    public MedewerkerMeldingContext () : base(ConnectionStringKey)
    {

    };

    public DbSet<medewerker> medewerker { get; set; }
    public DbSet<medewerker_melding> medewerker_melding { get; set; }
}

then

 using (var context = new MedewerkerMeldingContext())
        {



            var medewerkers = context.medewerker;
            var medewerker_meldings = context.medewerker_melding;

            var testQuery = from m in medewerkers
                            join mm in medewerker_meldings on m.ID equals mm.medewerkerID
                            where m.ID == id
                            select new MedewerkerMeldingViewModel
                            {
                                ID = m.ID,
                                roepnaam = m.roepnaam,
                                voorvoegsel = m.voorvoegsel,
                                achternaam = m.achternaam,
                                tussenvoegsel = m.tussenvoegsel,
                                meisjesnaam = m.meisjesnaam,
                                datum_in_dienst = m.datum_in_dienst,
                                datum_uit_dienst = m.datum_uit_dienst,
                                aantal_km_woon_werk = m.aantal_km_woon_werk,
                                maandag = m.maandag,
                                ma_van = m.ma_van,
                                ma_tot = m.ma_tot,
                                dinsdag = m.dinsdag,
                                di_van = m.di_van,
                                di_tot = m.di_tot,
                                woensdag = m.woensdag,
                                wo_van = m.wo_van,
                                wo_tot = m.wo_tot,
                                donderdag = m.donderdag,
                                do_van = m.do_van,
                                do_tot = m.do_tot,
                                vrijdag = m.vrijdag,
                                vr_van = m.vr_van,
                                vr_tot = m.vr_tot,
                                zaterdag = m.zaterdag,
                                za_van = m.za_van,
                                za_tot = m.za_tot,
                                zondag = m.zondag,
                                zo_van = m.zo_van,
                                zo_tot = m.zo_tot,
                                medewerkerID = mm.medewerkerID,
                                meldingID = mm.meldingID,
                                datum_van = mm.datum_van,
                                datum_tot = mm.datum_tot,
                                MM_ID = mm.ID
                            };
            Debug.WriteLine("Debug testQuery" + testQuery);

            var getQueryResult = testQuery.ToList();
            Debug.WriteLine("Debug getQueryResult:   " + getQueryResult);

            var resultDictionary = getQueryResult.GroupBy(x => x.ID).ToDictionary(y => y.Key, z => z.ToList());
            Debug.WriteLine("resultDictionary: " + resultDictionary);

            var firstItem = resultDictionary.Values.First();
            Debug.WriteLine("FirstItem: " + firstItem);

            var Entity = new newEntity
            {
                //ID = firstItem.ID,
                ID = firstItem.Select(x => x.ID).First(),
                roepnaam = firstItem.Select(x => x.roepnaam).First(),
                voorvoegsel = firstItem.Select(x => x.voorvoegsel).First(),
                achternaam = firstItem.Select(x => x.achternaam).First(),
                tussenvoegsel = firstItem.Select(x => x.tussenvoegsel).First(),
                meisjesnaam = firstItem.Select(x => x.meisjesnaam).First(),
                datum_in_dienst = firstItem.Select(x => x.datum_in_dienst).First(),
                datum_uit_dienst = firstItem.Select(x => x.datum_uit_dienst).First(),
                aantal_km_woon_werk = firstItem.Select(x => x.aantal_km_woon_werk).First(),
                maandag = firstItem.Select(x => x.maandag).First(),
                ma_van = firstItem.Select(x => x.ma_van).First(),
                ma_tot = firstItem.Select(x => x.ma_tot).First(),
                dinsdag = firstItem.Select(x => x.dinsdag).First(),
                di_van = firstItem.Select(x => x.di_van).First(),
                di_tot = firstItem.Select(x => x.di_tot).First(),
                woensdag = firstItem.Select(x => x.woensdag).First(),
                wo_van = firstItem.Select(x => x.wo_van).First(),
                wo_tot = firstItem.Select(x => x.wo_tot).First(),
                donderdag = firstItem.Select(x => x.donderdag).First(),
                do_van = firstItem.Select(x => x.do_van).First(),
                do_tot = firstItem.Select(x => x.do_tot).First(),
                vrijdag = firstItem.Select(x => x.vrijdag).First(),
                vr_van = firstItem.Select(x => x.vr_van).First(),
                vr_tot = firstItem.Select(x => x.vr_tot).First(),
                zaterdag = firstItem.Select(x => x.zaterdag).First(),
                za_van = firstItem.Select(x => x.za_van).First(),
                za_tot = firstItem.Select(x => x.za_tot).First(),
                zondag = firstItem.Select(x => x.zondag).First(),
                zo_van = firstItem.Select(x => x.zo_van).First(),
                zo_tot = firstItem.Select(x => x.zo_tot).First()
            };

            Debug.WriteLine("Entity: " + Entity);

            var plainValues = resultDictionary.Values.SelectMany(x => x).ToList();

            var resultSchedule = plainValues.Select(x => new medewerker_melding
            {
                medewerkerID = x.medewerkerID,
                meldingID = x.meldingID,
                datum_van = x.datum_van,
                datum_tot = x.datum_tot,
                ID = x.MM_ID
            }).ToList();

            Entity.medewerker_melding = resultSchedule;

}

Answer 2

You need to check whether MedewerkerMeldingContext dbC = new MedewerkerMeldingContext(); is implementing IEnumerable<T> else, you will not be able to preform the desired action on the table.

This kind of error (Could not find an implementation of the query pattern) usually occurs when:

• You are missing LINQ namespace usage (using System.Linq)
• Typeyou are querying does not implement IEnumerable<T>

What i'd recommend, first check the namespace. Second check for the IEnumerable<T> implementation.

Your query is good enough, you take the context and perform the linq, no issue here. It is 90% that you forgot the namespace since context is already implementing the IEnumerable<T> interface.