Entity framework raw query to linq

Question

I have raw query that goes like this:

string upit = "select f.idFilm as idFIlm, f.naziv as nazivFilm, z.naziv as nazivZanr, f.idZanr as idZanr, f.godina as godina, f.slika, f.klip <br>
from video_klub_sema.film as f <br>
inner join video_klub_sema.zanr as z on z.idZanr=f.idZanr <br>
inner join video_klub_sema.kopija as k on f.idFilm=k.idFilm where ";

if (checkBox1.Checked)
    upit += "k.nije_tu=0 ";
else
    upit += " k.nije_tu in (0,1)";

if (comboBoxGodina.SelectedIndex == 0)
    upit += " and f.godina in (select distinct godina from video_klub_sema.film)";
else
    upit += " and f.godina=" + comboBoxGodina.SelectedItem.ToString();

if (comboBoxZanr.SelectedIndex == 0)
    upit += " and f.idZanr in (select idZanr from video_klub_sema.zanr)";
else
    upit += " and f.idZanr= (select idZanr from video_klub_sema.zanr where naziv = '" + comboBoxZanr.SelectedItem.ToString() + "')";

if (textNaziv.Text != "")
    upit += " and f.naziv like '%" + textNaziv.Text + "%'";

upit += " GROUP BY f.naziv, f.idFilm, z.naziv, f.idZanr, f.godina, f.slika, f.klip";

Can this query be converted into entity-framework linq query?

score 0 · Accepted Answer · edited May 23 '17 at 12:22

Yes it is possible. Check out these Linq samples:

http://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

the IN operator can be translated into the Any extension method.

the LIKE can be translated into the Contains extension method.

Like Operator in Entity Framework?

Not the best solution, but I hope it will be a good starting point:

 var query = from f in filmTable
                     join z in zanrTable on f.idZanr equals z.idZanr
                     join k in kopijaTable on f.idFilm equals k.idFilm
                     select new
                                {
                                    idFIlm=f.idFilm ,
                                    nazivFilm=f.naziv,
                                    nazivZanr=z.naziv,
                                    idZanr=f.idZanr,
                                    godina=f.godina,
                                    f.slika, 
                                    f.klip,
                                    k.nije_tu
                                };

        if (checkBox1.Checked)
            query =query.Where(k=>k.nije_tu==0);
        else
            query = query.Where(k => k.nije_tu == 0|| k.nije_tu==1);

        var godinaSubQuery = filmTable.Select(f => f.godina).Distinct();
        if (comboBoxGodina.SelectedIndex == 0)
            query = query.Where(f => godinaSubQuery.Contains(f.godina));
        else
            query= query.Where(f=>f.godina== comboBoxGodina.SelectedItem.ToString());

        if (comboBoxZanr.SelectedIndex == 0)
            query = query.Where(f => zanrTable.Select(p => p.idZanr).Contains(f.idZanr));
        else
           query =query.Where(f=>f.idZanr==zanrTable.FirstOrDefault(z=>z.naziv== comboBoxZanr.SelectedItem.ToString().idZanr));

        if (textNaziv.Text != "")
            query =query.Where(f=>f.nazivFilm.Contains(textNaziv.Text));

It helped a lot. I had to fix some things, but the code is great. Thank you. Now I have one more problem. Look at if (checkBox1.Checked) query =query.Where(k=>k.nije_tu==0); else query = query.Where(k => k.nije_tu == 0|| k.nije_tu==1).Distinct(); For example, I have movie "Golden Eye" that has k.nije_tu both =1 and =0, and now I get this movie two times in the list. I guess there should be something with Contains, but I don't know how to fix it in there. — filipst, Sep 09 '13 at 14:19
The problem is that i'm just guessing what your query should do. I guess nije_tu is a boolean value wether the copy of the film is rented or not. In this case this code is unnecessarily else upit += " k.nije_tu in (0,1)"; — Herr Kater, Sep 09 '13 at 19:46
Look at this question, i think they are dealing with the same problem. http://stackoverflow.com/questions/16799008/entity-framework-returning-distinct-records-after-join — Herr Kater, Sep 09 '13 at 19:55

Entity framework raw query to linq

1 Answers1