Using SQL instead of LINQ in Telerik Open Access

Question

I'm using Telerik Open Access. I have two separate projects that have Open Access data and then a third project that has the bulk of my code. I've been working on a way to convert a simple (at least I thought it was) SQL query to LINQ so that I can get the data I need. I have not been successful. I've had to break a single LINQ query into separate queries, because of the need for the Trim() function (I think). This has led to a lengthy piece of code and I'm still not getting the same results as my SQL query.

So my question is, is there anyway to use SQL instead of LINQ to access the data in the Open Access projects? If so, can you show me the syntax to do that for my query?

If it is not possible to use SQL, can you show me show me the proper way to convert my SQL query into LINQ so that I get the same results?

Thank you.

My SQL query is

SELECT DISTINCT us2.ccustno, us2.dispname, us2.csiteno, so.s1_name
FROM [DALubeDeacom].[dbo].[dmbill] bi
      INNER JOIN [DALubeDeacom].[dbo].[dmso1] so
            ON bi.bi_s1id = so.s1_id
      INNER JOIN [DALubeNew].[dbo].[usersecurity] us2
            ON so.s1_name = us2.cparentno
WHERE
        us2.ctype = 'JOBSITE'
    AND us2.csiteno is not null
    AND us2.csiteno != ''
    AND bi.bi_smid = '22'
ORDER BY us2.csiteno

My LINQ query is

public List<DataModelSample> GetLocationsBySalesNo(string salesNo)
        {
            int iSalesNo = int.Parse(salesNo.Trim());
            try
            {
                var dmso = (
                    from so in deacom.Dmso1
                    join qt in deacom.Dmbills
                        on so.S1_id equals qt.Bi_s1id
                    where qt.Bi_smid == iSalesNo
                    select new Dmso1
                    {
                        S1_id = so.S1_id
                        , S1_name = so.S1_name.Trim()

                    }
                    );

                    var usec = (
                    from us in dbContext.Usersecurities
                    where us.Cparentno != null && us.Cparentno.Trim() != ""     && us.Ctype.Trim() == "JOBSITE" && us.Csiteno.Trim() != ""
                    select new Usersecurity
                    {
                        Ccustno = us.Ccustno.Trim(),
                        Csiteno = us.Csiteno.Trim(),
                        Dispname = us.Dispname.Trim(),
                        Cparentno = us.Cparentno.Trim()
                    }
                    );

                    var customers =
                    (
                        from us in usec
                        join so in dmso
                        on us.Cparentno equals so.S1_name
                        select us
                    );

                    customers = customers.GroupBy(x => x.Csiteno).Select(x => x.First());

                    List<DataModelSample> listLocations =
                        (
                            from c in customers
                            select new DataModelSample
                            {
                                customerID = c.Ccustno
                                ,
                                origLocationName = c.Csiteno + " " + c.Dispname
                                ,
                                origLocationID = c.Csiteno
                            }
                        ).OrderBy(x => x.origLocationID).ToList();

                return listLocations.ToList();
            }
            catch (Exception ex)
            {
                throw ex;
            }

        } // GetLocationsBySalesNo(userInfo.csalesno)

Edit 1 - 2-19-16

Tried a suggestion by ViktorZ. His query was similar to the one I first tried. It returned the error "Identifier 'Ctype' is not a parameter or variable or field of 'DALube_DeacomModel.Dmbill'. If 'Ctype' is a property please add the FieldAlias or Storage attribute to it or declare it as a field's alias." From an online search, it looked like this was do to "extended fields". I don't seemed to be using such fields. The only way I could get around this error was to break it into the smaller LINQ queries in my original question, which didn't produce the right results. Any suggestions?

Here's the code:

var query = (from bill in deacom.Dmbills
            join so in deacom.Dmso1 on bill.Bi_s1id equals so.S1_id
            join us in dbContext.Usersecurities on so.S1_name equals us.Cparentno
            where us.Ctype == "JOBSITE"
                && us.Csiteno != null
                && us.Csiteno != string.Empty
                && bill.Bi_smid == iSalesNo
            select new
            {
                ccustno = us.Ccustno.Trim(),
                dispname = us.Dispname.Trim(),
                csiteno = us.Csiteno.Trim(),
                s1_name = so.S1_name.Trim()
            }).Distinct();

Answer 1

One very crude approximation of your SQL query is:

    var query = (from bill in deacom.Bills
            join so in deacom.LubeDeacom on bill.bi_s1id equals so.s1_id
            join us in deacom.UserSecurity on so.s1_name equals us.cparentno
            where us.ctype = "JOBSITE"
                && us.csiteno != null
                && us.csiteno != string.Empty
                && bill.smid = '22'
            order by us.csiteno
            select new
            {
                us.ccustno.Trim(),
                us.dispname.Trim(),
                us.csiteno.Trim(),
                so.s1_name.Trim()
            }).Distinct();

// to check the translation result
string sql = query.ToString()
// to get the results
var result = query.ToList()

If this is not working for you, you can always fall back to Telerik Data Access ADO.NET API. Here is a documentation article how to use it.