Entity Framework Join multiple tables is slow

Question

first time I was trying to join this many tables using Entity Framework.

Everything works well but loading time is about 1 minute, very bad. I dont know if is some different solution to join this many tables, or something what could help.

It is about 5000 records in database. I think in future number of this records can be more higher. I need something what will take it. Thanks.

Code below:

var JoinedTopCars = db.CustomerAdvertisement.Where(o => o.Top == true)
 .Join(
db.CarManufacturerName,
n => n.CarManufacturerId,
q => q.CarManufacturerId,
(n, q) => new
{
    n,
    q,
})
 .Join(
db.CarManufacturerModel,
o => o.n.CarModelId,
j => j.CarModelId,
(o, j) => new
{
    j,
    o,
}).Where(y => y.j.CarManufacturerId == y.o.q.CarManufacturerId)
.Join(
db.TypeOFFuel,
e => e.o.n.FuelId,
r => r.Id,
(e, r) => new
{
    e,
    r,
})
.Join(
db.TypeOFGearBox,
t => t.e.o.n.GearBoxId,
i => i.Id,
(t, i) => new
{
    t,
    i,
})
.Join(
db.Country,
y => y.t.e.o.n.CountryOfOrigin,
u => u.Id,
(y, u) => new
{
    y,
    u,
})
 .Join(
db.TypeOFChassis,
c => c.y.t.e.o.n.ChassisId,
d => d.Id,
(c, d) => new
{
    c,
    d,
})
.Join(
db.CarDoors,
e => e.c.y.t.e.o.n.CarDoorsId,
f => f.Id,
(e, f) => new
{
    e,
    f,
})
.Join(
db.TypOfMovement,
g => g.e.c.y.t.e.o.n.MovementId,
f => f.Id,
(g, f) => new
{
    g,
    f,
})
.Join(
db.Area,
i => i.g.e.c.y.t.e.o.n.AreaOfOrigin ?? 0,
f => f.Id,
(i, f) => new
{
    i,
    f,
})
.Join(
db.District,
j => j.i.g.e.c.y.t.e.o.n.OkresOfOrigin ?? 0,
f => f.Id,
(j, f) => new
{
    j,
    f,
})
.Join(
db.CarColor,
k => k.j.i.g.e.c.y.t.e.o.n.CarColorId,
x => x.Id,
(k, x) => new JoinedTopCars
{
    Id = k.j.i.g.e.c.y.t.e.o.n.Id,
    Objem = k.j.i.g.e.c.y.t.e.o.n.cm3,
    Carname = k.j.i.g.e.c.y.t.e.o.q.CarName,
    CarModel = k.j.i.g.e.c.y.t.e.j.CarModel,
    Typ = k.j.i.g.e.c.y.t.e.o.n.ModelType,
    Color = x.ColorName,
    Karoseria = k.j.i.g.e.d.ChassisName,
    Dvere = k.j.i.g.f.NumberOfDoors,
    Pohon = k.j.i.f.Movement,
    VIN = k.j.i.g.e.c.y.t.e.o.n.VIN,
    Metalic = k.j.i.g.e.c.y.t.e.o.n.Metalic,
    Poskodene = k.j.i.g.e.c.y.t.e.o.n.Crashed,
    Pojazdne = k.j.i.g.e.c.y.t.e.o.n.Drivable,
    DPH = k.j.i.g.e.c.y.t.e.o.n.DPH,
    Leasing = k.j.i.g.e.c.y.t.e.o.n.Leasing,
    Emisie = k.j.i.g.e.c.y.t.e.o.n.Emmisions,
    Spotreba = k.j.i.g.e.c.y.t.e.o.n.Consumption,
    Km = k.j.i.g.e.c.y.t.e.o.n.KM,
    Rok = k.j.i.g.e.c.y.t.e.o.n.DateOfOrigin.ToString(),
    Vykon = k.j.i.g.e.c.y.t.e.o.n.HP,
    Palivo = k.j.i.g.e.c.y.t.r.Fuel,
    Prevodovka = k.j.i.g.e.c.y.i.GearBox,
    Krajina = k.j.i.g.e.c.u.CountryName,
    Okres = k.f.DistrictName,
    Kraj = k.j.f.AreaName,
    Vybava = k.j.i.g.e.c.y.t.e.o.n.Equipment,
    Popis = k.j.i.g.e.c.y.t.e.o.n.Description,
    Kontakt = k.j.i.g.e.c.y.t.e.o.n.ContInfo,
    ZobrazMeno = k.j.i.g.e.c.y.t.e.o.n.ShowName,
    ZobrazCislo = k.j.i.g.e.c.y.t.e.o.n.ShowPhone,
    Cena = k.j.i.g.e.c.y.t.e.o.n.Price,
    TitleImage = k.j.i.g.e.c.y.t.e.o.n.TitlePhoto,
})
.OrderByDescending(z => z.Id)
.Take(15);

EDIT: I make what @romfir write bellow and decrase loading time from 1 minute to 3 sec.

1. Create SQL view
2. insert SQL query with JOINed tables (if you expect empty value use LEFT JOIN)
3. Update ADO.NET EF model, .edmx file
4. use this SQL View simmilar like table

Answer 1

You can try to create View in the database, like this:

CREATE VIEW my_view_name
AS
SELECT
    CustomerAdvertisement.ID as CustomerAdvertisementID -- 'as Name' is optional
    CustomerAdvertisement.cm3
    -- other columns You want to include
FROM 
    CustomerAdvertisement
JOIN CarManufacturerName 
    on CarManufacturerName.CarManufacturerId = CustomerAdvertisement.CarManufacturerId
JOIN SomeTable
    on some_condition
-- other joins

WHERE
    CustomerAdvertisement.Top = true
    and other_conditions

After creating view that matches Your criteria, You can scaffold it, and then use in Your code.