Help converting to LINQ!

Question

I am having many issues converting this SP to LINQ, i am using Linq to Entities.

The first problem is the NULL Int's, i can send a SQLInt32.Null to SQL in my code using the SQLTypes, but i can't find a way to do it with Linq. this will solve my problems returning all records even the integer is null or empty like i do it with a SQL Connection.

I can build a Linq expression with no problems using t.MyInt = _SomeInt or t.MyString.Contains(_SomeString), but i have the same probles with null values.

Hope my English is not confusing to understand!

ALTER PROCEDURE   ys_GetSeekIdentificacao
(	
	@NumOrdem int = NULL,
	@NumProcesso int = NULL,
	@Nome nvarchar(150) = NULL
)
as
if @NumOrdem IS NULL AND 
   @NumProcesso IS NULL AND
   @Nome IS NULL 
begin
  select distinct
    dbo.tblMainDAT.NumOrdem, 
	 dbo.tblMainDAT.NumProcesso, 
	 dbo.tblMainDAT.Nome
  from dbo.tblMainDAT
  where     
    (NumOrdem = ISNULL(@NumOrdem, NumOrdem)) AND 
    (NumProcesso = ISNULL(@NumProcesso, NumProcesso)) AND 
    (Nome LIKE ISNULL ('%' + @Nome, Nome) + '%')
  order by Nome
end
else
BEGIN
   select distinct
    dbo.tblMainDAT.NumOrdem, 
	 dbo.tblMainDAT.NumProcesso, 
	 dbo.tblMainDAT.Nome
  from dbo.tblMainDAT
  where     
    (NumOrdem = ISNULL(@NumOrdem, NumOrdem)) AND 
    (NumProcesso = ISNULL(@NumProcesso, NumProcesso)) AND 
    (Nome LIKE ISNULL ('%' + @Nome, Nome) + '%')
  order by Nome
end

GO

Thanks to All!
Joao

Answer 1

int? numOrdem = null;
int? numProcesso = null;
string nome = null;

var someQuery =
(
from d in tblMainDat
where (numOrdem == null || d.NumOrdem == numOrdem)
   && (numProcesso == null || d.NumProcesso == numProcesso)
   && (nome == null || d.Nome.Contains(nome))
orderby d.Nome
select new { d.NumOrdem, d.NumProcesso, d.Nome }
).Distinct();

---

Kristofer - Huagati Systems Co., Ltd.
Cool tools for Linq-to-SQL and Entity Framework:
huagati.com/dbmltools (add-in with new features for Visual Studio 2008's L2S and EF designers)
huagati.com/L2SProfiler (Query profiler for Linq-to-SQL and LLBLGen Pro)

Answer 2

You save me hours of work!, thanks

Regards
Joao

Answer 3

Hi KristoferA!

Here it is the complete solution

	public class Utentes : IUtentes
	{
        public List<DumpData.DumpPesquisarUtentes> GetPesquisarUtentes(int? numOrdem, int? numProcesso, string nome)
        {
            numOrdem = null;
            numProcesso = null;
            nome = null;

            using (SocialEF db = new SocialEF())
            {
                var qPesquisarUtentes =
                (
                from d in db.tblMainDAT
                where (numOrdem == null || d.NumOrdem == numOrdem)
                   && (numProcesso == null || d.NumProcesso == numProcesso)
                   && (nome == null || d.Nome.Contains(nome))
                orderby d.Nome
                select new DumpData.DumpPesquisarUtentes
                {
                    dNumOrdem = d.NumOrdem,
                    dNumProcesso = d.NumProcesso,
                    dNome = d.Nome
                }).Distinct();
                return qPesquisarUtentes.ToList();
            }
        }
		public void DoWork()
		{
		}
	}